Remove identity or formula with out dropping and recreating table

Question

What I want is to set or remove identity or formula for a column of table in sql , sql generate this code :

CREATE TABLE dbo.Tmp_Table
    (
    a int NOT NULL IDENTITY (1, 1)
    )      ON [PRIMARY]
GO
ALTER TABLE dbo.Tmp_Table SET (LOCK_ESCALATION = TABLE)
GO

SET IDENTITY_INSERT dbo.Tmp_Table ON

GO
IF EXISTS(SELECT * FROM dbo.[Table])
     EXEC('INSERT INTO dbo.Tmp_Table (a)
        SELECT a FROM dbo.[Table] WITH (HOLDLOCK TABLOCKX)')
GO
SET IDENTITY_INSERT dbo.Tmp_Table OFF
GO
DROP TABLE dbo.[Table]
GO
EXECUTE sp_rename N'dbo.Tmp_Table', N'Table', 'OBJECT'

I wrote a script to set or remove identity or formula with out dropping a table and recreating it because I don't have all of the column's information such as primary key or foreign key. (I want to create this such as set NULL or not NULL that you just write a column name and a column type).

How can i do this? Is it possible to do it?

http://stackoverflow.com/questions/8230257/sql-server-remove-identity-from-a-column-in-a-table — user2864740, May 15 '14 at 05:54

score 1 · Answer 1 · answered May 15 '14 at 07:51

Short answer is that you can't do it easily. If you want to drop an identity specification, you have to drop and re-create the column (which is less invasive than dropping the table, but still a pain). To keep the data you would need to create a copy of the column without the identity specification and then copy the data across before dropping the original column and renaming the new one.

The problem with that is if you have a foreign key referencing that column then you need to drop that first and re-create it afterwards. Luckily you can get all the information about a column by querying the system catalog views. The ones you'd be interested in are:

sys.columns
sys.foreign_keys
sys.foreign_key_columns

You might also want sys.indexes and within that you'll want to check is_primary_key = 1 to get information on a primary key and to get the PK columns you'll need to look at sys.index_columns. I won't detail how to join them all (they are all linked) as you can find that in the MSDN documentation.

It's a proper pain in the neck to get rid of an identity specification, I feel your pain - I've been there myself.

Remove identity or formula with out dropping and recreating table

1 Answers1