Generate dynamic DDL primary key definitions from system views

generate-dynamic-ddl-primary-key-definitions.sql

SELECT 'ALTER TABLE '+ QUOTENAME(SCHEMA_NAME(t.schema_id)) + '.' + QUOTENAME(t.name)
	+ ' ADD  CONSTRAINT ' + QUOTENAME(i.name) 
	+ ' PRIMARY KEY CLUSTERED (' 
	+ pk.ColumnList + ')'
	, i.*
FROM sys.tables t
	INNER JOIN sys.indexes i on i.object_id = t.object_id
    CROSS APPLY (SELECT DISTINCT STUFF((SELECT ', ' +  QUOTENAME(c.name) + ' ASC '
				FROM sys.index_columns ic
					INNER JOIN sys.columns c ON c.column_id = ic.column_id AND c.object_id = ic.object_id
				WHERE ic.index_id = i.index_id
					AND ic.object_id = i.object_id
				FOR XML PATH('')),1,1,'') AS ColumnList
				)pk 
WHERE i.is_primary_key = 1