SQL Server: Displaying Foreign Key Constraint Name for all the tables in a database in SQL Server using Information Schema views

Dear All,

Information Schema Views are defined in SQL-92 standard as a way to view metadata in a database. These views are there in sql server since version 7.0, at that time they were the original system tables. Now, to comply with the SQL-92 standard, all these views are there in a schema called INFORMATION_SCHEMA. We can use these views to see objects that are compatible with SQL-92 standard. One such query could be to see the Foreign Key relationships for all the tables in a database. We can use the following query:

SELECT
RC.CONSTRAINT_NAME as ConstraintName,
TC.TABLE_CATALOG as DatabaseName,
TC.TABLE_SCHEMA as SchemaName,
TC.TABLE_NAME as TableName,
KCU.COLUMN_NAME as ColumnName,
RTC.TABLE_CATALOG as ReferenceedDatabaseName,
RTC.TABLE_SCHEMA as ReferencedSchemaName,
RTC.TABLE_NAME as ReferencedTableName,
RTC_COLS.COLUMN_NAME as ReferencedColumnName
FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS RC
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC
ON RC.CONSTRAINT_CATALOG = TC.CONSTRAINT_CATALOG
AND RC.CONSTRAINT_SCHEMA = TC.CONSTRAINT_SCHEMA
AND RC.CONSTRAINT_NAME = TC.CONSTRAINT_NAME
AND TC.CONSTRAINT_TYPE = 'FOREIGN KEY'
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS RTC ON RC.UNIQUE_CONSTRAINT_CATALOG = RTC.CONSTRAINT_CATALOG
AND RC.UNIQUE_CONSTRAINT_SCHEMA = RTC.CONSTRAINT_SCHEMA
AND RC.UNIQUE_CONSTRAINT_NAME = RTC.CONSTRAINT_NAME
AND RTC.CONSTRAINT_TYPE = 'PRIMARY KEY'
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE KCU ON RC.CONSTRAINT_NAME = KCU.CONSTRAINT_NAME
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE RTC_COLS ON RTC.CONSTRAINT_NAME = RTC_COLS.CONSTRAINT_NAME

There are many ways of getting this result set including the system catalogs. This is using Information Schemas.

 

Regards

Rahul Sharma

Like us on FaceBook Follow us on Twitter | Join the fastest growing SQL Server group on FaceBook

Follow me on TwitterFollow me on FaceBook

Leave a Reply

Your email address will not be published. Required fields are marked *