Adding a primary key to some outputted tables in the database
I have a database with over 600 tables. Some of the tables have primary keys and some do not. How can I dynamically:
1. Scroll through all tables
2. select the ones that don't have primary keys
3. add an auto-incrementing field
4. make this field the primary key
I assume it will 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
+3
source to share
1 answer
This query excludes all tables that have either a primary key or an identity column, after which it adds an identity column and a primary key to 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 tables that are already defined in an identity column, you can use this query to set that identity column as the primary key (because you cannot have two identity columns in 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);
+3
source to share