INFORMATION SCHEMA. KEY_COLUMN_USAGE
– Identifies columns which have constrained keys
CREATE VIEW INFORMATION_SCHEMA.KEY_COLUMN_USAGE
AS
SELECT
db_name() AS CONSTRAINT_CATALOG,
schema_name(f.schema_id) AS CONSTRAINT_SCHEMA,
f.name AS CONSTRAINT_NAME,
db_name() AS TABLE_CATALOG,
schema_name(p.schema_id) AS TABLE_SCHEMA,
p.name AS TABLE_NAME,
col_name(k.parent_object_id, k.parent_column_id)
AS COLUMN_NAME,
k.constraint_column_id AS ORDINAL_POSITION
FROM
sys.foreign_keys f JOIN sys.foreign_key_columns k
ON k.constraint_object_id = f.object_id
JOIN sys.tables p ON p.object_id = f.parent_object_id
UNION
SELECT
db_name() AS CONSTRAINT_CATALOG,
schema_name(k.schema_id) AS CONSTRAINT_SCHEMA,
k.name AS CONSTRAINT_NAME,
db_name() AS TABLE_CATALOG,
schema_name(t.schema_id) AS TABLE_SCHEMA,
t.name AS TABLE_NAME,
col_name(c.object_id, c.column_id) AS COLUMN_NAME,
c.key_ordinal AS ORDINAL_POSITION
FROM
sys.key_constraints k JOIN sys.index_columns c
ON c.object_id = k.parent_object_id
AND c.index_id = k.unique_index_id
JOIN sys.tables t ON t.object_id = k.parent_object_id
————–