0

My company has an application with a bunch of database tables that used to use a sequence table to determine the next value to use. Recently, we switched this to using an identity property. The problem is that in order to upgrade a client to the latest version of the software, we have to change about 150 tables to identity. To do this manually, you can right click on a table, choose design, change (Is Identity) to "Yes" and then save the table. From what I understand, in the background, SQL Server exports this to a temporary table, drops the table and then copies everything back into the new table. Clients may have their own unique indexes and possibly other things specific to the client, so making a generic script isn't really an option.

It would be really awesome if there was a stored procedure for scripting this task rather than doing it in the GUI (which takes FOREVER). We made a macro that can go through and do this, but even then, it takes a long time to run and is error prone. Something like: exec sp_change_to_identity 'table_name', 'column name'

Does something like this exist? If not, how would you handle this situation?

Update: This is SQL Server 2008 R2.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Trinculo
  • 1,950
  • 1
  • 17
  • 22
  • 1
    What versions of SQL Server? If 2012+ it will likely be much less painful to change them to [`SEQUENCE`](http://technet.microsoft.com/en-us/library/ff878091.aspx) rather than `IDENTITY` as that is just a question of adding a default to all the relevant columns. – Martin Smith Jan 13 '14 at 17:46
  • This is SQL Server 2008 R2. – Trinculo Jan 13 '14 at 17:52
  • 1
    It looks like really you can't :( http://stackoverflow.com/questions/11897007/alter-table-column-for-primary-key-and-identity – Mike M Jan 13 '14 at 18:37
  • 1
    Or maybe you can... http://stackoverflow.com/questions/6084572/how-to-set-auto-increment-after-creating-a-table-without-any-data-loss – Mike M Jan 13 '14 at 18:39
  • Do you have data in these tables? Are you using these columns in foreign keys? If so, this is more refactoring task than simple transformation. – Serg Jan 13 '14 at 19:10
  • 1
    @MikeM - You can, but it would still be quite a lot of work to write a script that creates the correct table structure to switch into that covers all the possible SQL Server features the original table might be using. – Martin Smith Jan 13 '14 at 20:10
  • @martinsmith - Indeed. It is great info, in any case. – Mike M Jan 13 '14 at 20:19
  • @Serg - Yes, there are foreign keys, custom indexes, statistics, and more. – Trinculo Jan 14 '14 at 02:11
  • I guess the question is, "What would you do?". Our solution is to create a macro to run through the GUI and do the correct clicks, etc. It works, but seems like such a slow mess that I thought I'd check to see if anyone else has ever tackled this problem using a more efficient method. – Trinculo Jan 14 '14 at 02:18

1 Answers1

0

This is what SSMS seems to do:

  1. Obtain and Drop all the foreign keys pointing to the original table.
  2. Obtain the Indexes, Triggers, Foreign Keys and Statistics of the original table.
  3. Create a temp_table with the same schema as the original table, with the Identity field.
  4. Insert into temp_table all the rows from the original table (Identity_Insert On).
  5. Drop the original table (this will drop its indexes, triggers, foreign keys and statistics)
  6. Rename temp_table to the original table name
  7. Recreate the foreign keys obtained in (1)
  8. Recreate the objects obtained in (2)
thepirat000
  • 12,362
  • 4
  • 46
  • 72
  • Do you know if there is any way to script this process? That is the problem I am running into. I have a pretty good idea of what it does, but the only option (that I see) is through the GUI which is very slow, especially for 150 tables. – Trinculo Jan 14 '14 at 02:15