Is there a way add auto increment in all tables from a specific database at once?

Question

I am trying to add auto increment in all existing tables in a specific database, and I can do that going through the table design, flagging the identity option, but in this case I have to do it table per table, and there is a lot of tables. Is there a way to do that automatically?

You could use a cursor and iterate over all tables, check if there is an identity, get the value, and then increment. Give it a shot and ask another question if you get stuck with the specifics of the problem. — Igor, Jul 17 '17 at 12:10
So, I want to update all primary keys, that are not identity. I see the script generated in the table design when I flag "Is Identity", and it drops the table and create new one with identity, and populate it with the data that was in the old table, dropping too the foreign keys that it may have and recreating it. I am looking for some possibilities to do that(having to reacreate the table or not) for all tables to transform the primary key to auto increment at once, — William, Jul 17 '17 at 12:22
@William, adding an identity column to every table mindlessly isn't a good practice from a data modeling perspective. — Dan Guzman, Jul 17 '17 at 12:27
Ah, that last comment clarifies your technical intent. I would not recommend this. Think about all your existing foreign keys and also the constraints already in place. You would have to update all the relationships across your tables. I would not rely on a simple script to do this as it is not a simple change. — Igor, Jul 17 '17 at 12:32
@DanGuzman It makes sense, but this is the way that the database is modeled. — William, Jul 17 '17 at 12:34
@Igor When I do that through the table desing, it do it to me automatically(recreate the table, including foreign keys and data) — William, Jul 17 '17 at 12:37
You can [create a script to add an identity column to an existing table](https://stackoverflow.com/q/108211/1260204) (*that does not have one yet*), you should not have to drop/recreate the table to do this. What you can't create a generic/templated script for is changing the FK constraints across all the related tables. — Igor, Jul 17 '17 at 12:42
@Igor He's not adding a column with the identity property. He's adding the identity property it to an existing column. That *does* require either a drop and re-create, or [some mildly complex partition shuffling](https://stackoverflow.com/a/1730868/696808). — Bacon Bits, Jul 17 '17 at 12:45
@Igor I have tried to do that, but what I want is change the primary key field to primary key and auto increment. I could create this another identity column, and then alter values that are in the primary key to this new identity column and the drop the old column, but I cannot alter values of a identity column. — William, Jul 17 '17 at 12:49
I don't believe you're going to find an automated option for doing this for multiple tables. The change script that SSMS creates when you do this in table designer is already doing a ton of work you'd have to recreate for any other solution. Frankly, I wouldn't trust myself to do it as correctly as SSMS. — Bacon Bits, Jul 17 '17 at 12:56
However, if it were a large enough number of tables, I would create a completely new database with the corrected schema. Ensure that everything in the new database is present and correct. Then, set identity insert to on all tables in the new db, copy the data over, set all the identity inserts off, and then move the new db to the old db with DETACH/ATTACH or BACKUP/RESTORE. In other words, I'd literally rebuild the database from the ground up because old schema had been completely trashed. It would take *a lot* for me to decide to do that in a production system, however. — Bacon Bits, Jul 17 '17 at 12:59
I'd only do the DETACH/ATTACH or BACKUP/RESTORE if I absolutely needed to change the database file names or database names. I'd actually prefer to just use the new database as a new database for the application. — Bacon Bits, Jul 17 '17 at 13:01

M. Ayoub · Answer 1 · 2017-07-17T14:29:09.650

It can be done by using a 'cursor', but you need to have all the columns that you need to add auto increment to in the same name as ID

Declare @Table nvarchar(50), @script nvarchar(100)

    DECLARE cur CURSOR FORWARD_ONLY READ_ONLY LOCAL FOR
        SELECT TABLE_SCHEMA + '.' + TABLE_NAME as 'Table' FROM INFORMATION_SCHEMA.TABLES where TABLE_NAME not in ('sysdiagrams') -- You can exclude any table from this process by adding it on the where statement

    OPEN cur
    FETCH NEXT FROM cur INTO @Table
    WHILE @@FETCH_STATUS = 0 BEGIN
        -- The sql command to alter a Table and add Identity to it, you can change ID by any column in your tables 
        set @script = 'Alter Table '+ @Table +' ADD ID INT IDENTITY'


        EXEC sp_executesql @script

        FETCH NEXT FROM cur INTO @Table
    END

    CLOSE cur
    DEALLOCATE cur

Edit 1 : According to what you asked for in the comment

Declare @Table nvarchar(50), @script nvarchar(100), @primarykey_name nvarchar(20)

DECLARE cur CURSOR FORWARD_ONLY READ_ONLY LOCAL FOR
    SELECT TABLE_SCHEMA + '.' + TABLE_NAME as 'Table' FROM INFORMATION_SCHEMA.TABLES where TABLE_NAME not in ('sysdiagrams') -- You can exclude any table from this process by adding it here 

OPEN cur
FETCH NEXT FROM cur INTO @Table
WHILE @@FETCH_STATUS = 0 BEGIN
    -- Find Primary key for the current Table and set it to @primarykey_name
    Set @primarykey_name = (SELECT c.NAME FROM sys.key_constraints kc INNER JOIN sys.index_columns ic ON kc.parent_object_id = ic.object_id  and kc.unique_index_id = ic.index_id
        INNER JOIN sys.columns c ON ic.object_id = c.object_id AND ic.column_id = c.column_id
        WHERE kc.name='PK_'+ substring(@Table, 5, LEN(@Table)-4) and kc.type = 'PK')

    -- The sql command to alter a Table and add Identity to the primarykey of each table
    set @script = 'Alter Table '+ @Table +' ADD ' + @primarykey_name + ' INT IDENTITY'

    print @script
    --EXEC sp_executesql @script

    FETCH NEXT FROM cur INTO @Table
END

CLOSE cur
DEALLOCATE cur

In this, I will add a new column that is identity, what I need would be something like this, but with alter column in the primary key. — William, Jul 17 '17 at 14:00
i edited it hope that work for you (it's not the perfect solution) — M. Ayoub, Jul 17 '17 at 14:30
By the way this won't add any new column, it will only alter the column state. — M. Ayoub, Jul 17 '17 at 14:40
It is adding a new primary key, I am trying to modify the primary column. What I need was something like that, but with alter column, instead add column. By the way, the cursor through tables worked :) — William, Jul 17 '17 at 15:42

score 0 · Accepted Answer · answered Jul 20 '17 at 13:22

Copied from my comments per request:

I don't believe you're going to find an automated option for doing this for multiple tables. The change script that SSMS creates when you do this in table designer is already doing a ton of work you'd have to recreate for any other solution. Frankly, I wouldn't trust myself to do it as correctly as SSMS.

However, if it were a large enough number of tables, I would create a completely new database with the corrected schema. Ensure that everything in the new database is present and correct. Then, set identity insert to on all tables in the new db, copy the data over, set all the identity inserts off, and then move the new db to the old db with DETACH/ATTACH or BACKUP/RESTORE. In other words, I'd literally rebuild the database from the ground up because old schema had been completely trashed. It would take a lot for me to decide to do that in a production system, however.

I'd only do the DETACH/ATTACH or BACKUP/RESTORE if I absolutely needed to change the database file names or database names. I'd actually prefer to just use the new database as a new database for the application. That would also mean I could swap back to the old database pretty quickly if I ran into trouble.

Is there a way add auto increment in all tables from a specific database at once?

2 Answers2