How to drop IDENTITY

  • 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

  • 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

  • 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.

  • 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...

  • 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

  • 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.

  • 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/

  • I agree with you, but that’s the only safe solution from MS. Hope they will add a trick in the next Ver 🙂

    Shas3

  • 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/

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply