SQL command to add & Drop Indentity column

[dokken]

I would like to run a .SQL script to drop the identity attribute of a column on 10 tables. Does anybody know how I would do this?

Thanks
Paul

 

[dbmsguy]

I do not believe that you can alter an 'identity' out of a column. you can however turn it off if you like. this will allow you to insert a specific value.

command is

SET IDENTITY_INSERT [database.[owner.]]{table} {ON | OFF}

depending on what you want to do, it may help. only one table at a time can have identity insert on.

Paul

http://www.dbmsguy.com

 

[dokken]

I would just like to turn the identity property off. Would I use this statement in SQL:
SET IDENTITY_INSERT dbo.tblUser OFF

 

[dokken]

I tried in in SQL Query Analyzer and it doesn't work.

 

[dbmsguy]

you want to turn 'identity insert' on not off. Paul

http://www.dbmsguy.com

 

[tlbroadbent]

You cannot remove the Identity property using a simple T-SQL statement. You can use Enterprise Manager to remove the Identity property. EM then creates a new table without the Identity property, copies all the data, deletes the old table and renames the new table to the old name. You could write a T-SQL script to do the same.

I know it is not elegant or efficient but that is current state of T-SQL. Terry L. Broadbent
Programming and Computing Resources

http://tlbroadbent.home.attbi.com/prog.htm