Generate PK drops and creates using TSQL

Here’s just a couple of queries I used to generate PK drops and creates using the sys.key_constraints view. I wanted to do this for a database using Poor Mans Partitioning.

Generate drops…

SELECT 'ALTER TABLE ' + OBJECT_SCHEMA_NAME(t.[object_id]) + '.' + t.name + ' DROP CONSTRAINT ' + c.[name] + ';'
FROM sys.key_constraints c
INNER JOIN sys.tables t
	ON t.object_id = c.parent_object_id
WHERE t.name LIKE 'table_pattern_%'
AND c.[type] = 'PK';

Generate creates…

SELECT 'ALTER TABLE ' + OBJECT_SCHEMA_NAME(t.[object_id]) + '.' + t.name + ' ADD CONSTRAINT '
		 + ' PK_' + t.name + ' PRIMARY KEY CLUSTERED (new, columns, in , pk) FILLFACTOR=100;'
FROM sys.tables t
WHERE t.name LIKE 'table_pattern_%';