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

November 1, 2006 by pinaldave

UPDATE : SQL SERVER – 2005 – Find Tables With Foreign Key Constraint in Database

This is very long query. Optionally, we can limit the query to return results for one or more than one table.

SELECT K_Table = FK.TABLE_NAME, FK_Column = CU.COLUMN_NAME, PK_Table = PK.TABLE_NAME, PK_Column = PT.COLUMN_NAME, Constraint_Name = C.CONSTRAINT_NAME FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS C INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS FK ON C.CONSTRAINT_NAME = FK.CONSTRAINT_NAME INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS PK ON C.UNIQUE_CONSTRAINT_NAME = PK.CONSTRAINT_NAME INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE CU ON C.CONSTRAINT_NAME = CU.CONSTRAINT_NAME INNER JOIN ( SELECT i1.TABLE_NAME, i2.COLUMN_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS i1 INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE i2 ON i1.CONSTRAINT_NAME = i2.CONSTRAINT_NAME WHERE i1.CONSTRAINT_TYPE = 'PRIMARY KEY' ) PT ON PT.TABLE_NAME = PK.TABLE_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')

Reference : Pinal Dave (http://blog.SQLAuthority.com)

Posted in Pinal Dave, SQL, SQL Authority, SQL Constraint and Keys, SQL Query, SQL Scripts, SQL Server, SQL Stored Procedure, SQL Tips and Tricks, T SQL, Technology | 43 Comments

43 Responses

on June 21, 2007 at 5:00 am Shashi

Excellenet Script
on July 2, 2007 at 2:12 am Danny

Perfect
on July 3, 2007 at 11:12 pm RANGARAJAN

REALLY HELPED ME.. GR8 SCRIPTING..
on July 9, 2007 at 9:16 pm anjali

can there be multiple primary key (in multiple tables) in response to one foreign key in a table
on August 30, 2007 at 7:41 am Mark

Hi
Thanks for your query, it set me off in the right direction :).
Much Appreciated.

I have rewritten the query to be slightly faster:

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

Hope this helps :)
-Mark
on August 30, 2007 at 7:44 am pinaldave

Mark,

Thanks for sharing the information. It is important to learn and share. I am glad that you follow the same understanding.

Very good script.

Kind Regards,
Pinal
on September 3, 2007 at 7:33 am Edwin

Hi Pinal,

Am a beginner in SQLServer and I find your script to be very helpful.

Do you have a similar script that lists all types of constraints, i.e. check constraints etc.?

Any pointers to the right direction will be very much appreciated.
on September 5, 2007 at 11:20 pm Kanika

Really fundoo, good that people like you believe in sharing information!
on September 6, 2007 at 4:01 am Subhash

Hi Pinal,

I used this script to find duplicate relationships. Thank you for this information sharing.
on September 16, 2007 at 7:31 am Prashant Pandey

Hey, this is really a good querry, We can use when we want to know that how many primary keys and forign keys are there in the database.

BUt as Subhash said that he used this querry to “find duplicate relationships” in the database.
Wht is mean by “duplicate relationships” ???
on September 25, 2007 at 3:39 am kannan

Hi,

What is the query used to display all tables names in sql server (Query analyzer) ?

Pls reply to this question? ASAP

thanks and regards
n.m.
on October 11, 2007 at 10:56 pm G2

Excellent, Very impressed
on October 23, 2007 at 2:33 am Mark

Hi there again

I was just re-looking at this script, and was wondering about something:

For the line in the script above that reads:
WHERE i1.CONSTRAINT_TYPE = ‘PRIMARY KEY’

Is this necessary, because don’t all foreign key constraints correspond to Primary Keys? In the REFERENTIAL_CONSTRAINTS table, the FK columns correspond to UNIQUE_CONSTRAINT…. columns. Can a FK correspond to any unique constraint or only a Primary Key constraint?

Thanks
on October 23, 2007 at 2:49 am Mark

Sorry, just to clarify the above comment:
The above mentioned where clause in your SQL is deftinately necessary because of the nested select statement.
Removing the CONSTRAINT_TYPE clause in my script above will not make it any faster.
I am just wondering what applies conceptually :)
on November 12, 2007 at 6:36 pm Tamer Mohammed

hi every one
Thank you alot i am a bigenier in developing software
i want your advice to help me go in this field
on February 7, 2008 at 5:20 am Pat

Nice! However, I don’t think it’s working properly on multi-column foreign keys.
on February 8, 2008 at 4:16 pm Santosh

This is not a comment rather question.

I went through the above post and found very interesting. I tried to modify it according to my problem but no luck.

Problem.

how to get the list of tables who has the FK relation ship with the given tables. once i get the list I want to delete the record from the parent table and set the references to null from the table where parents pk is referred as fk.

I am not using cascade delete since I don’t want to delete the complete row.

right now i am doing this by writing the simple SP and manually setting the null to the referenced tables.

Thanks you very much in advance.

-Santosh Maskar
on February 9, 2008 at 10:41 am Santosh Maskar

after spending some time on the given SQL I solved my problem.

Thanks a lot

-Santosh
on March 20, 2008 at 5:26 pm Bala

hi
I need to delete the paticular records from parent table without affecting the child table(will have more child table) and also the relationship between the records should be removed,

since am going to use the unaffected record in child table as parent table..
Hope u got it..
Your help is appreciated.

Thanks & Regards
Bala
on March 28, 2008 at 2:53 pm Johnson

Thank for the script. It really help me.

Thank

Regards.
Johnson
on April 5, 2008 at 5:11 am Richardz

This was a great tutorial! It was no problem at all to adapt this and create a routine that automatically fixed that pesky name mangling the occurs in Sql Server constraints ( so that FK__Trader__departme__4B7734FF becomes FK__Trader__department_id).

You helped me solve a real headache.
on April 22, 2008 at 1:15 pm Umer

Really Nice Article… helped me alot :)
on April 24, 2008 at 7:42 pm merix

This is good but if I have multiple columns in my foreign keys it will multiply the results so that 2 column returns 4 rows of data and 3->9 and so on.

Adding the following line to Marks solution will reduce the amount of lines
AND FK_COLS.ORDINAL_POSITION = PK_COLS.ORDINAL_POSITION

Otherwise this was really helpful.
on May 27, 2008 at 1:09 pm Daniel Harrison

Hi all,

Great script, really helpful. I was wondering, is there a way i can change this script so that I can get a sensible order of integrity?

Basically my problem is that we have this ’sync’ service that packages data up from remote sites and sends it to our central database. At the moment it just pipes all the data in an manual order that we set because we know about the constraints. What i would like is to be able to know which tables need to have their data first (ie tables that have their primary keys referenced as foreign keys in other tables).

Is there a way to do that?
on June 12, 2008 at 4:31 pm Charanpreet Singh

I needed a script which find all the table where primary key of a table is used in other tables as foreign key. i need to delete a record in master table ..
This script worked well for me.
thanks
on July 16, 2008 at 3:19 pm Charanpreet Singh

suppose i have number of select statements in a single sp in sqlserver 2005.
i need to know how many statements are there in that sp.
on August 14, 2008 at 11:11 am sameer

hi…

i want to delete rows from tables having “UserID” Coulmn in a databse named “Test” . but some tables are foreignkey constraints… how it can be done…

first i have to find all tables having UserID column. then delete the rows from tables containg foreign key constraint(i.e where UserID=’SOMETHING’)…after that delete the tables having primary key..in a database…

SUMMARY:: I HAVE TO CLEAR ALL THE ROWS FROM ALL THE TABLES HAVING USERID COLUMN IN A DATABASE WHERE SOME ARE FOREGNKEY CONSTRAINTS AND SOME ARE PRIMARY KEY…

HOW IT’S POSSIBLE…
on August 26, 2008 at 7:42 pm Sandesh S

Hey How about

EXEC SP_Fkeys @TableName
on August 27, 2008 at 5:34 pm Pradeep

Could we bind same default to multiple columns of the same table. If so then how.
on September 5, 2008 at 2:40 am Cowboy

PINAL, I have a somewhat related problem that i was wondering if you had a solution for

There are 4 tables with relationships to each other. ConferenceRoom, ConferenceRoomCapacity, TeleTrackPhone, and RPSLocation

The table ConferenceRoom stores attributes pertaining to conference rooms.

The table ConferenceRoomCapacity stores attributes pertaining to the maximum capacity of the conference room.

The table TeleTrackPhone contains different phone numbers. It stores ConferenceRoomID and RPSLocationID as FKs.

The table RPSLocation stoes info about different locations, city, state, etc.

The TeleTrackPhone table allows duplicate values for the ConferenceRoomID because a conference room can have more than one phone number.

This is the query that I run.

SELECT DISTINCT A.ConferenceRoomID, A.ConferenceRoomName, C.City, C.State
FROM ConferenceRoom As A, TeleTrackPhone As B, RPSLocation As C, ConferenceRoomCapacity As D
WHERE A.ConferenceRoomID = B.ConferenceRoomID
AND B.RPSLocationID = C.RPSLocationID
AND A.ConferenceRoomID = D.ConferenceRoomID

My problem is that it returns duplicates if the conference room has multiple phone numbers and i only want the query to return a list of conference rooms.
on October 3, 2008 at 8:14 pm Aniket Tamhane

Nice one… It helped me to a lot during SQL Server Replication Setup
on October 4, 2008 at 6:17 am Imran Mohammed

Hello,

This one is simpler, just gives you table_name and which type of key information and key name.

select OBJECT_NAME(PARENT_OBJ) TABLE_NAME, CASE WHEN XTYPE =’F’ THEN ‘FORIEGN KEY’ ELSE ‘PRIMARY KEY’ END KEY_TYPE , NAME KEY_NAME
from sysobjects where Xtype in (’F’ , ‘pK’) ORDER BY XTYPE DESC

I am using only sysobjects table to get this information.

Hope this helps.
Imran.
on October 14, 2008 at 8:09 am Rich

Fantastic script, thank you so much!
on October 28, 2008 at 10:54 pm Karim

Hi,
here’s another version (mine :-; ) to extract foreign keys.

Replace ‘TABLE_NAME’ with yours … !

Regards
Karim Laurent

select OBJECT_NAME(FKEYS.PARENT_OBJECT_ID) source ,PKCOLUMN_NAME= convert(sysname,col1.name), OBJECT_NAME(FKEYS.referenced_object_id) destination, FKCOLUMN_NAME = convert(sysname,COL2.name)

from

sys.columns COL1,

sys.columns COL2,

sys.foreign_keys FKEYS

inner join sys.foreign_key_columns KEY_COLUMN on (KEY_COLUMN.constraint_object_id = FKEYS.object_id)

where

COL1.object_id = FKEYS.referenced_object_id

AND COL2.object_id = FKEYS.parent_object_id

AND COL1.column_id = KEY_COLUMN.referenced_column_id

AND COL2.column_id = KEY_COLUMN.parent_column_id

AND OBJECT_NAME(FKEYS.PARENT_OBJECT_ID)=’TABLE_NAME’
on November 17, 2008 at 1:13 pm Dalbir

Thanks, Great scripting
on December 31, 2008 at 1:59 pm Smeet

MAKE IT MORE SHORT N SIMPLE
Will work on both 2000 and 2005
===========================

select object_name(constid) FKey_Name, object_name(fkeyid) Child_Table, c1.name FKey_Col,
object_name(rkeyid) Parent_Table, c2.name Ref_KeyCol
from sysforeignkeys s
inner join syscolumns c1
on ( s.fkeyid = c1.id
and s.fkey = c1.colid )
inner join syscolumns c2
on ( s.rkeyid = c2.id
and s.rkey = c2.colid )
on March 8, 2009 at 3:41 pm Karan Kang

Nice ..the script really helped me..!
Thanks..!
on March 17, 2009 at 12:32 pm Sanjay

I want to retrieve only limited data from database like limit is keyword in mysql which retrieve limited data according to our arguments. Like I want to load only 10 records from the database at first display in page. but I don’t want to using top. Because its create problem of paging. So please give me alternate solution for that’d have use row index but in that row index I have to write inner query and my data base I s to heavy.
on April 5, 2009 at 12:56 am Dinci

Excellent script that got me going in the right direction. Thank you so much.
on April 22, 2009 at 7:12 pm Wannabag

Excellent script thank you
on April 24, 2009 at 1:57 am Pablo Veliz

excelente, gracias por publicarlo.
on April 27, 2009 at 3:50 pm Dakshina Murthy

Hi,

It was great, it helped me in finding the duplicate FK for the same set of tables for the same set of fields.

With Regards
Dakshina Murthy
on May 22, 2009 at 6:43 pm Jaseem

Hi
How to retrieve all columns of the given table and its foreign key table.
Regards
Jaseem, A.