January 5, 2005 at 3:05 am
I have atable X with column Y Indentity(1,1). How can I removed identity from table X column Y using T-SQL?
I don't want to drop table X.
January 5, 2005 at 3:21 am
alter table X drop column Y
Hope this helps,
Kathi
Aunt Kathi Data Platform MVP
Author of Expert T-SQL Window Functions
Simple-Talk Editor
January 5, 2005 at 3:29 am
Assuming you want to keep the already existing data, your best bet is to use EM and remove the IDENTITY property there in table design view. Instead of clicking on Save, you might want to have a change script generated, to see what's going on behind the scenes. There's no direct way to drop this property via simple commands.
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
January 5, 2005 at 4:57 am
I completely misunderstood the question. Sorry about that.
Kathi
Aunt Kathi Data Platform MVP
Author of Expert T-SQL Window Functions
Simple-Talk Editor
January 5, 2005 at 5:33 am
Right, but I want to prepare a script for production server, so don;t want to do it using EM. Is there any direct method without droping Table and Column?
January 5, 2005 at 5:43 am
You can save the change script without the need to apply it to the table. That way you are able to schedule for off-peak hours, as I guess you intend to do.
Another common method would be to create a new column, update that column with the values in the IDENTITY column, drop the IDENTITY column, and rename the new column with the name of the old IDENTITY. In case you have indexes and/or constraints defined on the IDENTITY column, you need to recreate them, too. Again, it's quite easy to create an IDENTITY column, but it's a royal pain to get rid of it.
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
January 5, 2005 at 6:10 am
I think your idea can help. Thanks! Frank.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply