カラムに設定されているPKとUKのリストを、テーブルごとカラムごとにCSVで取得するクエリです。
select
col.object_id
,object_name(col.object_id) as table_name
,col.column_id
,col.name as column_name
,stuff((
select
concat(
','
,idx.name
)
from sys.indexes as idx
inner join sys.index_columns as idxcols ON
idx.object_id=idxcols.object_id
and
idx.index_id=idxcols.index_id
where
(
idx.is_primary_key=1
or
idx.is_unique=1
)
and
(
col.object_id=idxcols.object_id
and
col.column_id=idxcols.column_id
)
order by
idx.name
for xml path ('')
),1,1, '') as index_names
from sys.columns as col
出力はこんな感じです
