INFORMATION SCHEMA TABLE_CONSTRAINTS
– Identifies table constraints for tables where the current user has any permission
CREATE VIEW INFORMATION_SCHEMA.TABLE_CONSTRAINTS
AS
SELECT
db_name() AS CONSTRAINT_CATALOG,
schema_name(c.schema_id) AS CONSTRAINT_SCHEMA,
c.name AS CONSTRAINT_NAME,
db_name() AS TABLE_CATALOG,
schema_name(t.schema_id) AS TABLE_SCHEMA,
t.name AS TABLE_NAME,
CASE c.type
WHEN ‘C ‘ THEN ‘CHECK’
WHEN ‘UQ’ THEN ‘UNIQUE’
WHEN ‘PK’ THEN ‘PRIMARY KEY’
WHEN ‘F ‘ THEN ‘FOREIGN KEY’
END AS CONSTRAINT_TYPE,
‘NO’ AS IS_DEFERRABLE,
‘NO’ AS INITIALLY_DEFERRED
FROM
sys.objects c LEFT JOIN sys.tables t
ON t.object_id = c.parent_object_id
WHERE
c.type IN (’C’ ,’UQ’ ,’PK’ ,’F')
———–