Adding primary key to some exising tables in a database

Question

I have a database with over 600 tables. Some of the tables have primary keys and some do not. How can I dynamically:

1. loop through all tables
2. select ones that do not have primary keys
3. add an auto incremented field
4. make this field the primary key

I would presume it would be a combination of the following:

USE MyDataBase; 
GO
-- List all tables without primary constraint
SELECT i.name AS IndexName, 
    OBJECT_NAME(ic.OBJECT_ID) AS TableName, 
    COL_NAME(ic.OBJECT_ID,ic.column_id) AS ColumnName
FROM sys.indexes AS i
INNER JOIN sys.index_columns AS ic
ON i.OBJECT_ID = ic.OBJECT_ID
AND i.index_id = ic.index_id
WHERE i.is_primary_key = 0
GO

-- add new auto incremented field
ALTER TABLE MyTable 
ADD PK_ID BIGINT IDENTITY;
GO

-- create new primary key constraint
ALTER TABLE MyTable 
ADD CONSTRAINT PK_ID PRIMARY KEY NONCLUSTERED (PK_ID);
GO

Answer 1

This query, will exclude all tables that have either a primary key or an identity column, it will then add identity column and primary key on the remaining objects

DECLARE @PKScript AS VARCHAR(max) = '';
SELECT @PKScript += 
    '  ALTER TABLE ' + QUOTENAME(SCHEMA_NAME(obj.SCHEMA_ID))+'.'+ QUOTENAME(obj.name) + 
                      ' ADD PK_ID BIGINT IDENTITY;' +
     ' ALTER TABLE ' + QUOTENAME(SCHEMA_NAME(obj.SCHEMA_ID))+'.'+QUOTENAME(obj.name) + 
                     ' ADD CONSTRAINT PK_ID_' + obj.name+ ' PRIMARY KEY NONCLUSTERED (PK_ID) '
 FROM sys.objects obj
 WHERE object_id not in
     (select parent_object_id
      from sys.key_constraints 
      where type = 'PK'
      UNION
      Select object_id
      from sys.identity_columns
      )
      AND type = 'U'
--PRINT (@PKScript);
EXEC(@PKScript);  

For the tables that already have identity column defined, you might use this query to set this identity column to be the primary key (because you can't have two identity columns on the same table)

DECLARE @PKScript2 VARCHAR(max)='';

SELECT @PKScript2 += ' ALTER TABLE ' + QUOTENAME(SCHEMA_NAME(obj.SCHEMA_ID))+'.'+
       QUOTENAME(obj.name) + ' ADD CONSTRAINT PK_' + icol.name +'_'+ obj.name+
       ' PRIMARY KEY NONCLUSTERED (' + QUOTENAME(icol.name) + ')' + CHAR(13)
FROM sys.identity_columns icol INNER JOIN 
     sys.objects obj on icol.object_id= obj.object_id
WHERE NOT EXISTS (SELECT * FROM sys.key_constraints k
                  WHERE k.parent_object_id = obj.object_id
                        AND k.type = 'PK')
      AND obj.type = 'U'
--PRINT (@PKScript2);
EXEC(@PKScript2);