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

Data Platform Virtual Summit 2020

Subscribe to SQLServerGeeks YouTube channel. If you want more learning content in your inbox, subscribe to SQLServerGeeks Bulletin.

SQLServerGeeks YouTube | SQLServerGeeks Bulletin | SQLServerGeeks Twitter

Leave a Reply

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