MSSQL add IDENTITY by altering all tables columns

Question

I have a database in which all the tables have column named ID. I need to alter all tables in database and add identity to these columns. Is it possible with some query ? Or do I have to do it manually?

Thank you very much.

Answer 1

Unfortunately in SQL Server you cannot add identity property to existing columns. You need to drop an existing one, then create new with this property. You can automate this task by quering system tables and using dynamic sql. But if you already have some data in ID column this will make things more tricky because you need to preserve existing data. Here is a script for a single table, automating this for all tables in database using dynamic sql will be kinda tricky...

Table Test_table has 2 columns: id and val.

-- Move data to temp storage
SELECT ID,
   VAL
  INTO #temp_table
  FROM dbo.test_table

-- Remove data from original table
DELETE
  FROM dbo.test_table

-- Drop and Create ID column
ALTER TABLE dbo.test_table
DROP COLUMN ID

ALTER TABLE dbo.test_table
ADD ID int IDENTITY(1,1)

-- Move data back to original table
SET IDENTITY_INSERT dbo.test_table ON

INSERT INTO dbo.test_table (ID, VAL)
SELECT ID, VAL
FROM #temp_table

DECLARE @MaxID int
SELECT @MaxID = MAX(ID) + 1
FROM dbo.test_table

SET IDENTITY_INSERT dbo.test_table OFF

-- Reseed IDENTITY property
DBCC CHECKIDENT ('dbo.test_table', RESEED, @MaxID)

Answer 2

There is no way to do this for all tables. Here's what I'd do: Use T-SQL to generate a (big) script that performs all the changes, then manually run that script.

You can add the identity property to existing columns without data movement using SWITCH.