Journey to SQL Authority with Pinal DaveSQL, SQL Server, MySQL, Big Data and NoSQLMenuSkip to contentHomeAll ArticlesInterviewsBlog StatsContactResumePerformanceCommunity RulesCopyrightToolsPluralsightSpotlight – DellIderaRed GateApexSQL ToolsDevart SQL Server ToolsIgniteFreeEmbarcaderoMelissadataManage EngineVideosVideo CoursesBooksSearchMagazineFix Your SQL ServerJanuary 4, 2015 by Pinal DaveInterview Question of Week #001 – Script to List Foreign Key Relationships and Constraint NameTwo years ago I wrote a book SQL Server Interview Questions and Answers with my very close friend Vinod Kumar. It is a very popular book of mine and has sold many thousands of copies so far. Every single day I still get quite a many different interview questions in email and users wants me to answer them. I have been collecting them for quite a while and in this new year, I am starting this new series, which will take a one interview question sent by readers and will try to answer in short and simple words. The goal of this series is to build a knowledge on the subject. If you want to pass any interview, you should know much more than this question and answers. You should have real world experience. Read this blog post over here before you continue reading this story.Well, let us start this series. In this week’s episode we will try to answer following question -Question: Write a script to display foreign key relationships and name of the constraint for each table in the database for SQL Server 2005 and later version.Answer: Here is the script which will display foreign key relationships and name of the constraint for each table in the database.SQL Server 2005 and later versionSELECT 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_idReference LinkIf you execute the above query with Adventure sample database, it will give following result set.SQL Server 2000SELECTK_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,4Reference LinkIf you h