SQL Server – How to Remove Identity Specification for Multiple Tables

identitysql serversql-server-2008

I have 109 tables in my database. Now I am migrating to Amazon server with identity insert and 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 have

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 but for all remaining tables.

Best Answer

There is no built-in command to remove the identity property from a column in SQL Server. You may have to create a new table, copy all the data across, and then rename the table. This can be time-consuming and awkward, especially if you have things like foreign key constraints, schema-bound functions, indexed views and so on.

That said, there is a way to avoid copying the data at least, but again this will not work in all scenarios, particularly if you have foreign keys. Example follows:

Example table and data

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
INSERT dbo.tbl_acc
    (a2, a3, a4)
VALUES
    (1, 'a', 'b'),
    (2, 'c', 'd');

Meta data transfer and rename

-- Copy of the source table specification,
-- but without the IDENTITY property
-- Note the PRIMARY KEY constraint name
-- also has to be different
CREATE TABLE dbo.tbl_acc2
    (
    a1     NUMERIC (18) NOT NULL,
    a2     NUMERIC (18) NULL,
    a3     VARCHAR (4000) NULL,
    a4     VARCHAR (3999) NULL,

    CONSTRAINT PK_tbl_acc2 PRIMARY KEY (a1)
    )
GO
BEGIN TRANSACTION
    -- Transfer the data as a metadata operation
    -- TODO: Add error handling
    ALTER TABLE dbo.tbl_acc
    SWITCH TO dbo.tbl_acc2;

    -- Drop the source table (now empty)
    DROP TABLE dbo.tbl_acc;

    -- Rename the table
    EXECUTE sys.sp_rename 
        @objname = N'dbo.tbl_acc2',
        @newname = N'tbl_acc',
        @objtype = 'OBJECT';

    -- Rename the primary key
    EXECUTE sys.sp_rename 
        @objname = N'dbo.tbl_acc.PK_tbl_acc2',
        @newname = N'PK_tbl_acc',
        @objtype = 'INDEX';
COMMIT TRANSACTION;
Related Question