CONSTRAINT COLUMN USAGE – INFORMATION SCHEMA
– Identifies tables AND columns that have constraints
CREATE VIEW INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE
AS
SELECT
KCU.TABLE_CATALOG,
KCU.TABLE_SCHEMA,
KCU.TABLE_NAME,
KCU.COLUMN_NAME,
KCU.CONSTRAINT_CATALOG,
KCU.CONSTRAINT_SCHEMA,
KCU.CONSTRAINT_NAME
FROM
INFORMATION_SCHEMA.KEY_COLUMN_USAGE KCU
UNION ALL
SELECT
db_name() AS TABLE_CATALOG,
schema_name(u.schema_id) AS TABLE_SCHEMA,
u.name AS TABLE_NAME,
col_name(d.referenced_major_id, d.referenced_minor_id)
AS COLUMN_NAME,
db_name() AS CONSTRAINT_CATALOG,
schema_name(k.schema_id) AS CONSTRAINT_SCHEMA,
k.name AS CONSTRAINT_NAME
FROM
sys.check_constraints k JOIN sys.objects u ON u.object_id = k.parent_object_id
JOIN sys.sql_dependencies d ON d.class = 1
AND d.object_id = k.object_id
AND d.column_id = 0
AND d.referenced_major_id = u.object_id
UNION ALL
SELECT
db_name() AS TABLE_CATALOG,
schema_name(t.schema_id) AS TABLE_SCHEMA,
t.name AS TABLE_NAME,
col_name(f.object_id, f.column_id) AS COLUMN_NAME,
db_name() AS CONSTRAINT_CATALOG,
schema_name(r.schema_id) AS CONSTRAINT_SCHEMA,
r.name AS CONSTRAINT_NAME
FROM
sys.objects t JOIN sys.columns f ON f.object_id = t.object_id
JOIN sys.objects r ON r.object_id = f.rule_object_id
——-