Skip to content

Instantly share code, notes, and snippets.

@ksakae1216 ksakae1216/sql3
Created May 17, 2017

Embed
What would you like to do?
--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
You can’t perform that action at this time.