Query to Display Foreign Key Relationships and Name of the Constraint for Each Table in Database

Query to Display Foreign Key Relationships and Name of the Constraint for Each Table in Database

From: http://blog.sqlauthority.com/2006/11/01/sql-server-query-to-display-foreign-key-relationships-and-name-of-the-constraint-for-each-table-in-database/

-- Query foreign keys from a table

SELECT CONS.CONSTRAINT_CATALOG AS DB
     , CONS.CONSTRAINT_SCHEMA AS [SCHEMA]
     , CONS.TABLE_NAME AS [TABLE]
     , CONS.CONSTRAINT_TYPE AS [TYPE]
     , CONS.CONSTRAINT_NAME AS CONST_NAME
     , COLS.COLUMN_NAME AS [COLUMN]
  FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS CONS
       LEFT JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE COLS 
           ON CONS.TABLE_NAME=COLS.TABLE_NAME
           AND CONS.CONSTRAINT_SCHEMA=COLS.CONSTRAINT_SCHEMA
           AND CONS.CONSTRAINT_NAME=COLS.CONSTRAINT_NAME
 WHERE CONS.TABLE_NAME='MyTableName'


--This sample have some issue so that some tables is missing in the query result when they without a foreign key.
SELECT
CONSTRAINT_NAME = REF_CONST.CONSTRAINT_NAME,
TABLE_CATALOG = FK.TABLE_CATALOG,
TABLE_SCHEMA = FK.TABLE_SCHEMA,
TABLE_NAME = FK.TABLE_NAME,
COLUMN_NAME = FK_COLS.COLUMN_NAME,
REFERENCED_TABLE_CATALOG = PK.TABLE_CATALOG,
REFERENCED_TABLE_SCHEMA = PK.TABLE_SCHEMA,
REFERENCED_TABLE_NAME = PK.TABLE_NAME,
REFERENCED_COLUMN_NAME = PK_COLS.COLUMN_NAME
FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS REF_CONST
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS FK
ON REF_CONST.CONSTRAINT_CATALOG = FK.CONSTRAINT_CATALOG
AND REF_CONST.CONSTRAINT_SCHEMA = FK.CONSTRAINT_SCHEMA
AND REF_CONST.CONSTRAINT_NAME = FK.CONSTRAINT_NAME
AND FK.CONSTRAINT_TYPE = 'FOREIGN KEY'
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS PK ON REF_CONST.UNIQUE_CONSTRAINT_CATALOG = PK.CONSTRAINT_CATALOG
AND REF_CONST.UNIQUE_CONSTRAINT_SCHEMA = PK.CONSTRAINT_SCHEMA
AND REF_CONST.UNIQUE_CONSTRAINT_NAME = PK.CONSTRAINT_NAME
AND PK.CONSTRAINT_TYPE = 'PRIMARY KEY'
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE FK_COLS ON REF_CONST.CONSTRAINT_NAME = FK_COLS.CONSTRAINT_NAME
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE PK_COLS ON PK.CONSTRAINT_NAME = PK_COLS.CONSTRAINT_NAME
---- optional:
ORDER BY
1,2,3,4
WHERE PK.TABLE_NAME='something' WHERE FK.TABLE_NAME='something'
WHERE PK.TABLE_NAME IN ('one_thing', 'another')
WHERE FK.TABLE_NAME IN ('one_thing', 'another')


--Sample 2  this sample will list all tables with constrain keys name.
SELECT OBJECT_NAME(parent_obj) table_name, name as key_name
FROM sysobjects WHERE xtype in('F', 'PK')