0

I am having total 109 table in Database and now i am migrating to amazon server with identity insert and now i want to remove auto increment column specification for all tables. How set auto increment column i.e Identity specification to no in SQL server 2008 for all tables.

For Example

I am having 

CREATE TABLE dbo.tbl_acc
    (
    a1     NUMERIC (18) IDENTITY NOT NULL,
    a2     NUMERIC (18) NULL,
    a3     VARCHAR (4000) NULL,
    a4     VARCHAR (3999) NULL,

    CONSTRAINT PK_tbl_acc PRIMARY KEY (a1)
    )
GO

And now i would like to remove IDENTITY. Not only for this remaining all tables.

Dinesh Reddy Alla
  • 1,677
  • 9
  • 23
  • 47
  • There is no easy way to remove the identity specification. See the answers here http://stackoverflow.com/a/1730868/73226 – Martin Smith Jan 02 '15 at 12:00

1 Answers1

0

Once the IDENTITY property is set, it cannot be removed. You can disable it temporarily in a session with SET IDENTITY_INSERT <table_name> ON/OFF, but there's no way to permanently convert it back a numeric column.

Here's how I did it in similar situations:

  1. Drop the primary key constraint
  2. Add another numeric column to your table (let's call it a10) and set its value to that of a1
  3. Drop a1
  4. Rename a10 to a1

You can build a dynamic SQL script to do this for all tables. The list of tables can be queried from INFORMATION_SCHEMA.TABLES, their constraints from INFORMATION_SCHEMA.TABLE_CONSTRAINTS.

Code Different
  • 90,614
  • 16
  • 144
  • 163