Created
December 21, 2021 10:34
-
-
Save ax4413/2fa0b33ee3c3dde1ce11815cd2b08e2f to your computer and use it in GitHub Desktop.
Generate dynamic DDL primary key definitions from system views
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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 |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment