July 2, 2003 at 2:03 pm
What would be the way to drop an IDENTITY option from a column? Say, the column is a primary key related to other tables through foreign keys. There is data is the table as well. But I want the IDENTITY option to go away forever.
Thank you for your help.
Michael
July 2, 2003 at 2:22 pm
I guess there is no direct way to drop the identity. You have to remove the PK/FK relation and create a dummy table with the same definition as your identity table with out Identity. Add the data from your real table. Drop the identity table and rename the dummy table to the original and add Keys
Shas3
July 2, 2003 at 2:40 pm
Well, I could just add another column to that table , copy data from the IDENTITY column into it, drop the DIENTITY column and recreate it, then copy data back and drop the extra column. But to do that I would need to drop all FK as well. It gets too complicated.
Thanks anyway.
July 2, 2003 at 3:25 pm
July 2, 2003 at 4:17 pm
Well, it's a funny link that suggests to use Enterprise Manager. What if I need to do it for more then 100 client databases where each one has 19 tables to be taken care of? What if some of the tables are large so Enterprise Manager would just halt forever? And so on...
July 2, 2003 at 5:59 pm
The problem is there isn't a simple ALTER TABLE command or anything of that sort to take care of removing an IDENTITY property.
The actual T-SQL (as can be seen through a profiler trace) is basically:
1) Create new table without identity column defined using a generated name
2) Copy data into new table
3) Drop old table
4) Rename new table to original name
It's easier to point people to EM.
K. Brian Kelley
http://www.truthsolutions.com/
Author: Start to Finish Guide to SQL Server Performance Monitoring
http://www.netimpress.com/shop/product.asp?ProductID=NI-SQL1
K. Brian Kelley
@kbriankelley
July 2, 2003 at 6:16 pm
Brian, thanks you forgot the foreign keys.
I am sure there is a way to do it through the system catalog if I allow updates to the system tables. Of course, it is dangerous but I would use it on development DB only. I just do not have time to look into it right now.
July 3, 2003 at 7:23 am
Try this:
Update syscolumns Set Colstat=0 Where id = object_id(<your table Name>) and name =(,Identity Column Name>)
Warning: Microsoft does not recommend directly modifying the system tables
Cheers,
Prithiviraj Kulasingham
http://preethiviraj.blogspot.com/
July 3, 2003 at 8:28 am
I agree with you, but that’s the only safe solution from MS. Hope they will add a trick in the next Ver 🙂
Shas3
July 3, 2003 at 9:44 pm
quote:
Update syscolumns Set Colstat=0 Where id = object_id(<your table Name>) and name =<Identity Column Name>
I tried this before posting and it works fine. Only thing we have to do is to configure the server to update system tables directly.
sp_Configure 'Allow Updates', 1
GO
reconfigure with override
G.R. Preethiviraj Kulasingham
Cheers,
Prithiviraj Kulasingham
http://preethiviraj.blogspot.com/
July 3, 2003 at 11:18 pm
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply