Navigation Menu

Skip to content

Instantly share code, notes, and snippets.

@ksakae1216
Created May 17, 2017 14:06
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save ksakae1216/87bd938f3d771714ce2f42134e1de520 to your computer and use it in GitHub Desktop.
Save ksakae1216/87bd938f3d771714ce2f42134e1de520 to your computer and use it in GitHub Desktop.
--3.最後にプライマリキーを追加
SELECT
'ALTER TABLE ' + tbls.name
+ ' ADD CONSTRAINT '
+ key_const.name
+ ' PRIMARY KEY(' + cols.name
+ case when cols2.name is not null then ' ,'+cols2.name else '' end
+ case when cols3.name is not null then ' ,'+cols3.name else '' end
+ case when cols4.name is not null then ' ,'+cols4.name else '' end
+ case when cols5.name is not null then ' ,'+cols5.name else '' end
+ case when cols6.name is not null then ' ,'+cols6.name else '' end
+ ');' AS "ADD プライマリキーを追加"
FROM
sys.tables AS tbls
INNER JOIN sys.key_constraints AS key_const ON
tbls.object_id = key_const.parent_object_id AND
key_const.type = 'PK'
AND tbls.name like 'TEST_%'
LEFT OUTER JOIN sys.index_columns AS idx_cols ON
key_const.parent_object_id = idx_cols.object_id
AND key_const.unique_index_id = idx_cols.index_id
AND idx_cols.key_ordinal = 1
LEFT OUTER JOIN sys.columns AS cols ON
idx_cols.object_id = cols.object_id
AND idx_cols.column_id = cols.column_id
LEFT OUTER JOIN sys.index_columns AS idx_cols2 ON
key_const.parent_object_id = idx_cols2.object_id
AND key_const.unique_index_id = idx_cols2.index_id
AND idx_cols2.key_ordinal = 2
LEFT OUTER JOIN sys.columns AS cols2 ON
idx_cols2.object_id = cols2.object_id
AND idx_cols2.column_id = cols2.column_id
LEFT OUTER JOIN sys.index_columns AS idx_cols3 ON
key_const.parent_object_id = idx_cols3.object_id
AND key_const.unique_index_id = idx_cols3.index_id
AND idx_cols3.key_ordinal = 3
LEFT OUTER JOIN sys.columns AS cols3 ON
idx_cols3.object_id = cols3.object_id
AND idx_cols3.column_id = cols3.column_id
LEFT OUTER JOIN sys.index_columns AS idx_cols4 ON
key_const.parent_object_id = idx_cols4.object_id
AND key_const.unique_index_id = idx_cols4.index_id
AND idx_cols4.key_ordinal = 4
LEFT OUTER JOIN sys.columns AS cols4 ON
idx_cols4.object_id = cols4.object_id
AND idx_cols4.column_id = cols4.column_id
LEFT OUTER JOIN sys.index_columns AS idx_cols5 ON
key_const.parent_object_id = idx_cols5.object_id
AND key_const.unique_index_id = idx_cols5.index_id
AND idx_cols5.key_ordinal = 5
LEFT OUTER JOIN sys.columns AS cols5 ON
idx_cols5.object_id = cols5.object_id
AND idx_cols5.column_id = cols5.column_id
LEFT OUTER JOIN sys.index_columns AS idx_cols6 ON
key_const.parent_object_id = idx_cols6.object_id
AND key_const.unique_index_id = idx_cols6.index_id
AND idx_cols6.key_ordinal = 6
LEFT OUTER JOIN sys.columns AS cols6 ON
idx_cols6.object_id = cols6.object_id
AND idx_cols6.column_id = cols6.column_id
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment