How can I set all ID fields to primary key?

Question

I have 15 tables in SQL Server. Each of them has an ID (int) column. How can I set all those ID fields to PRIMARY KEY and AUTOINCREMENT in a short or single SQL query?

You can't make an existing column an `IDENTITY` column, a column can only be specified as an `IDENTITY` column at the point it is created. — Thom A, Oct 15 '20 at 09:53
Related: [SQL Server add auto increment primary key to existing table](https://stackoverflow.com/q/4862385/1220550) — Peter B, Oct 15 '20 at 09:55
Also related: [How to add a primary key to all tables in SQL Server?](https://stackoverflow.com/q/48760581/1220550) - although this **adds** a new ID column + sets it as PK for each table. Converting is not so easy, as already mentioned above. — Peter B, Oct 15 '20 at 10:01
@Larnu thank you for your reply, I can still make them PRIMARY KEY and AUTOINCREMENT 1 by using SQL server management tool, but it takes time because I have to repeat that action 15 times in 15 tables. So I'm looking if there is a SQL query that can solve. — Trần Ngọc, Oct 15 '20 at 10:01
@TrầnNgọc You can have SSMS save the change you did as SQL script instead of applying it. You can then make 15 copies of that script, find/replace the table name in each copy, and then run all of them. — Peter B, Oct 15 '20 at 10:04
@TrầnNgọc SSMS doesn't "change" the column to be an `IDENTITY`, it completely recreates the table, with all it's `CONSTRAINT`s, `INDEX`es, etc, etc, as a new name, `INSERT`s the data from the existing table into that one, `DROP`s all the `CONSTRAINT`s, `INDEX`es, etc, and then renames the new table, and them, finally, recreates any foreign key constraints on other tables. That's why it takes so long. — Thom A, Oct 15 '20 at 10:08
@PeterB I rightclick an ID column, then select Set Primary Key, then in the colum propties ---> identity specification ---> (is Identity) : change to Yes, now how can I view the SQLquery string that can update the table instead of Ctrl + S and save it? — Trần Ngọc, Oct 15 '20 at 10:15

Peter B · Accepted Answer · 2020-10-15T11:28:20.863

The script needed to do this is actually not simple but very complicated. Fortunately SMSS can generate it for you (based on changes you make to 1 table). You can then make copies of that script, adapt those copies for each table, and then run them.

Steps:

Make sure that you disabled the SSMS option "Prevent saving changes that require table re-creation":
Then, make the needed changes in the Table Designer for 1 of your tables.
DO NOT press the Save button in the toolbar - instead, right click in the Designer window and choose the option Generate Change Script:

Save the SQL file, it will look something like this:

/* To prevent any potential data loss issues, you should review this script in detail before running it outside the context of the database designer.*/
BEGIN TRANSACTION
SET QUOTED_IDENTIFIER ON
SET ARITHABORT ON
SET NUMERIC_ROUNDABORT OFF
SET CONCAT_NULL_YIELDS_NULL ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
COMMIT
BEGIN TRANSACTION
GO
CREATE TABLE dbo.Tmp_Table1
  (
  ID int NOT NULL IDENTITY (1, 1)
  )  ON [PRIMARY]
GO
ALTER TABLE dbo.Tmp_Table1 SET (LOCK_ESCALATION = TABLE)
GO
SET IDENTITY_INSERT dbo.Tmp_Table1 ON
GO
IF EXISTS(SELECT * FROM dbo.Table1)
   EXEC('INSERT INTO dbo.Tmp_Table1 (ID)
      SELECT ID FROM dbo.Table1 WITH (HOLDLOCK TABLOCKX)')
GO
SET IDENTITY_INSERT dbo.Tmp_Table1 OFF
GO
DROP TABLE dbo.Table1
GO
EXECUTE sp_rename N'dbo.Tmp_Table1', N'Table1', 'OBJECT' 
GO
ALTER TABLE dbo.Table1 ADD CONSTRAINT
  PK_Table1 PRIMARY KEY CLUSTERED 
  (
  ID
  ) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

GO
COMMIT

Make 15 copies of the script file, then in each replace Table1 (or whatever was saved for you) with the respective 15 table names that you want to process.
Also edit each script to INSERT...SELECT the proper columns for each table, if your tables contain data already.
Run the 15 copies.

How can I set all ID fields to primary key?

1 Answers1