How to add Primary Key Constraint to Identity Columns to all the tables in SQL Server Database - SQL Server / TSQL Tutorial Part 63

Scenario:

You are working as SQL Server developer. You got this requirement in which you have to create Primary Key Constraint on all identity columns on all the tables in SQL Server database if does not exists.
The Primary Key name should be Pk_SchemaName_TableName_ColumnName.

Solution:

First of all we need to get the list of tables which has identity column and Primary Key Constraint is not created on them. we will be using system tables and system views to get all the tables with identity columns which do not have Primary Key Constraints. 

Then we are going to use the Cursor with dynamic SQL to generate Alter statement for table to add Primary Key Constraint and execute the script.

USE YourDatabaseName
GO

--Declare Variables
DECLARE @DatabaseName AS VARCHAR(128)
DECLARE @SchemaName AS VARCHAR(128)
DECLARE @TableName AS VARCHAR(128)
DECLARE @ColumnName AS VARCHAR(128)

DECLARE CUR CURSOR
FOR
--Get list of tables with Identity Column
--on which have no Primary Key
SELECT DB_Name() AS DatabaseName
    ,Schema_Name(Schema_id) AS TableSchema
    ,OBJECT_NAME(c.OBJECT_ID) AS TableName
    ,c.NAME AS ColumnName
FROM sys.columns c
INNER JOIN sys.tables t ON c.object_id = t.object_id
WHERE is_identity = 1
    AND type_desc = 'USER_TABLE'

EXCEPT

SELECT TC.Table_Catalog AS DatabaseName
    ,TC.Table_Schema AS TableSchema
    ,TC.Table_Name AS TableName
    ,CCU.Column_Name AS ColumnName
FROM information_Schema.Table_Constraints TC
INNER JOIN Information_Schema.constraint_column_usage CCU 

ON TC.Constraint_Name = CCU.Constraint_Name
    AND TC.Table_Name = CCU.Table_Name
WHERE Constraint_Type = 'PRIMARY KEY'

OPEN Cur

FETCH NEXT
FROM Cur
INTO @DatabaseName
    ,@SchemaName
    ,@TableName
    ,@ColumnName

WHILE @@FETCH_STATUS = 0
BEGIN
    --Build dynamic sql for each database 
    DECLARE @SQL VARCHAR(MAX) = NULL

    SET @SQL = 'Alter Table [' + @SchemaName + '].[' + @TableName + ']' + CHAR(10)
    SET @SQL += 'Add Constraint [Pk_' + @SchemaName 

+ '_' + @TableName 

             + '_' + @ColumnName + '] '
    SET @SQL += 'Primary Key ([' + @ColumnName + '])'

    EXEC (@SQL)

    PRINT @SQL

    FETCH NEXT
    FROM Cur
    INTO @DatabaseName
        ,@SchemaName
        ,@TableName
        ,@ColumnName
END

CLOSE Cur

DEALLOCATE Cur

If you like to just generate the alter statements for adding Primary Key Constraint, you can add comment to Exec(@SQL) part, The above script will print Alter statements and you can run on your convenience.

Also if you don't like to add the Schema name part to Primary Key constraint, you can remove @SchemaName.

I executed above query and it created Primary Key Constraint on Identity Columns where it was missing.

Alter scripts generated by script to add Primary Key Constraint to identity Columns

I verified the tables and Primary Key Constraints were created successfully.

How to create Primary Key Constraint on Identity Columns in SQL Server Database