主キー制約、ユニーク制約、外部キー制約の一覧を取得するクエリ

gistfile1.sql

with cte_columns as(
	select
		sys.tables.object_id,
		sys.columns.column_id,
		sys.tables.name as table_name,
		sys.columns.name as column_name
	from sys.tables
		inner join sys.columns
			on sys.tables.object_id = sys.columns.object_id
	where
		sys.tables.type = 'U'
)
-- primary, unique
select
	cte_columns.table_name as [Table],
	cte_columns.column_name as [Column],
	sys.key_constraints.type as [Type],
	sys.key_constraints.name as [Constraint],
	'' as [ReferencedTable],
	'' as [ReferencedColumn]
from sys.key_constraints
	inner join sys.index_columns
		on sys.key_constraints.parent_object_id = sys.index_columns.object_id and
			sys.key_constraints.unique_index_id = sys.index_columns.index_id		
	inner join cte_columns
		on sys.index_columns.object_id = cte_columns.object_id and
			sys.index_columns.column_id = cte_columns.column_id
union all
-- foreign
select
	cte_columns.table_name as [Table],
	cte_columns.column_name as [Column],
	'FK' as [Type],
	sys.foreign_keys.name as [Constraint],
	referenced_columns.table_name as [ReferencedTable],
	referenced_columns.column_name as [ReferencedColumn]
from sys.foreign_keys
	inner join sys.foreign_key_columns
		on sys.foreign_keys.object_id = sys.foreign_key_columns.constraint_object_id
	inner join cte_columns
		on sys.foreign_key_columns.parent_object_id = cte_columns.object_id and
			sys.foreign_key_columns.parent_column_id = cte_columns.column_id
	inner join cte_columns as referenced_columns
		on sys.foreign_key_columns.referenced_object_id = referenced_columns.object_id and
			sys.foreign_key_columns.referenced_column_id = referenced_columns.column_id
order by
	[Table], [Column], [Type];