Thursday, January 8, 2015

Script to List Foreign Key Relationships and Constraint Name

http://blog.sqlauthority.com/2015/01/04/interview-question-of-week-001-script-to-list-foreign-key-relationships-and-constraint-name/

SQL Server 2005 and later version

SELECT f.name AS ForeignKey,OBJECT_NAME(f.parent_object_id) AS TableName,COL_NAME(fc.parent_object_id,fc.parent_column_id) AS ColumnName,OBJECT_NAME (f.referenced_object_id) AS ReferenceTableName,COL_NAME(fc.referenced_object_id,fc.referenced_column_id) AS ReferenceColumnNameFROM sys.foreign_keys AS fINNER JOIN sys.foreign_key_columns AS fcON f.OBJECT_ID = fc.constraint_object_id
If you execute the above query with Adventure sample database, it will give following result set.

SQL Server 2000

SELECTK_Table = FK.TABLE_NAME,FK_Column = CU.COLUMN_NAME,PK_Table = PK.TABLE_NAME,PK_Column = PT.COLUMN_NAME,Constraint_Name = C.CONSTRAINT_NAMEFROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS CINNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS FK ON C.CONSTRAINT_NAME = FK.CONSTRAINT_NAMEINNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS PK ON C.UNIQUE_CONSTRAINT_NAME = PK.CONSTRAINT_NAMEINNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE CU ON C.CONSTRAINT_NAME = CU.CONSTRAINT_NAMEINNER JOIN (SELECT i1.TABLE_NAME, i2.COLUMN_NAMEFROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS i1INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE i2 ON i1.CONSTRAINT_NAME = i2.CONSTRAINT_NAMEWHERE i1.CONSTRAINT_TYPE = 'PRIMARY KEY') PT ON PT.TABLE_NAME = PK.TABLE_NAME---- optional:ORDER BY1,2,3,4
If you have a better answer, please leave a comment and I will include the answer with due credit.