sql3

--３．最後にプライマリキーを追加
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