SQL Server how to drop identity from a column

Question

Is there an easy way to remove an identity from a table in SQL Server 2005?

When I use Management Studio, it generates a script that creates a mirror table without the identity, copies the data, drops the table, then renames the mirror table, etc. This script has 5231 lines in it because this table/column have many FK relations.

I'd feel much more comfortable running a simple alter/drop. Any ideas?

EDIT
I think I'm just going to go with the 5,231 line script from Enterprise Manager. However, I'm going to break it up into smaller parts which I can run and control better. This table "behaves" strange, if you try to delete 1 row (even one you just inserted, which is not in any other FK table), you get this error:

delete MyTable where MyPrimaryKey=1234  

Msg 8621, Level 17, State 2, Line 1
    The query processor ran out of stack space during query optimization. Please simplify the query.

No doubt, all the FKs. We will halt all access to our application and run in single user mode when we make these schema and related application changes. However, we need this to run fast, and I need an idea of how long it will take. I guess that I'll just have to test, test, test.

Answer 1

You could add a column to the table that is not an identity column, copy the data, drop the original column, and rename the new column to the old column and recreate the indexes.

Here is a link that shows an example. Still not a simple alter, but it is certainly better than 5231 lines.

Answer 2

I don't believe you can directly drop the IDENTITY part of the column. Your best bet is probably to:

• add another non-identity column to the table
• copy the identity values to that column
• drop the original identity column
• rename the new column to replace the original column

If the identity column is part of a key or other constraint, you will need to drop those constraints and re-create them after the above operations are complete.

Answer 3

not an elegant solution but this might work:

• do a SELECT INTO from tableA to tableB
• use alter table to re-create any indexes, constraints ect.
• drop /rename TableA with sp_rename
• rename tableB to tableA

EDIT:

FYI about your DELETE error. Microsoft does not recommend a table have more then 253 FK constraints. Someone tried submitting this as a bug MS refused to fix stating that 'its by design' Here's the reference in BOL that states you shouldn't have > 253 constraints.

So i'd say a big ugly SSMS generated script is the least of your worries :-) Have your developers heard of normalization ?

Answer 4

You can also do it disabling temporarily constraint checks:

EXEC sp_MSForEachTable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL' GO

-- do your stuff

EXEC sp_MSForEachTable 'ALTER TABLE ? CHECK CONSTRAINT ALL' GO

Beware: it is dangerous :-)