Identify all of your foreign keys in a SQL Server database

By: Greg Robidoux | Read Comments (3) | Print Greg is the President of Edgewood Solutions and a co-founder of MSSQLTips.com. Related Tips: 1 | 2 | 3 | 4 | 5 | 6 | More

Problem
SQL Server Enterprise Manager and Management Studio are pretty good tools for giving you information about a particular object, but when you want to get information across your entire database or server this becomes a bit of a challenge.  There are several system stored procedures and now dynamic management views in SQL 2005 to provide some of the information, but there is still a bit of missing functionality if you want to get data across the database or server. One of these recent challenges was getting foreign key information across the entire database.  Management Studio has some nice additions where you can see the FK constraints on a table by table basis, but getting all of the foreign keys is a bit more a challenge.  There is a stored procedure called sp_fkeys, but this procedure requires you to include at least the primary key table name.  So how can you get a complete list?

Solution
There is a wealth of information in the system stored procedures and this is another good way of learning how things work behind the scenes.  By taking a look at the stored procedure sp_fkeys for SQL Server 2005 I noticed that it is not very complicated at all. When you pass in the parameters it basically uses the parameters to filter your selection.  By making a minor tweak to the SELECT statement we are able to get a list of all FKs in the entire database.  Here is the modified query for SQL Server 2005.

SQL Server 2005 Version

SELECT PKTABLE_QUALIFIER = CONVERT(SYSNAME,DB_NAME()),        PKTABLE_OWNER = CONVERT(SYSNAME,SCHEMA_NAME(O1.SCHEMA_ID)),        PKTABLE_NAME = CONVERT(SYSNAME,O1.NAME),        PKCOLUMN_NAME = CONVERT(SYSNAME,C1.NAME),        FKTABLE_QUALIFIER = CONVERT(SYSNAME,DB_NAME()),        FKTABLE_OWNER = CONVERT(SYSNAME,SCHEMA_NAME(O2.SCHEMA_ID)),        FKTABLE_NAME = CONVERT(SYSNAME,O2.NAME),        FKCOLUMN_NAME = CONVERT(SYSNAME,C2.NAME),        -- Force the column to be non-nullable (see SQL BU 325751)        --KEY_SEQ             = isnull(convert(smallint,k.constraint_column_id), sysconv(smallint,0)),        UPDATE_RULE = CONVERT(SMALLINT,CASE OBJECTPROPERTY(F.OBJECT_ID,'CnstIsUpdateCascade')                                          WHEN 1 THEN 0                                         ELSE 1                                       END),        DELETE_RULE = CONVERT(SMALLINT,CASE OBJECTPROPERTY(F.OBJECT_ID,'CnstIsDeleteCascade')                                          WHEN 1 THEN 0                                         ELSE 1                                       END),        FK_NAME = CONVERT(SYSNAME,OBJECT_NAME(F.OBJECT_ID)),        PK_NAME = CONVERT(SYSNAME,I.NAME),        DEFERRABILITY = CONVERT(SMALLINT,7)   -- SQL_NOT_DEFERRABLE FROM   SYS.ALL_OBJECTS O1,        SYS.ALL_OBJECTS O2,        SYS.ALL_COLUMNS C1,        SYS.ALL_COLUMNS C2,        SYS.FOREIGN_KEYS F        INNER JOIN SYS.FOREIGN_KEY_COLUMNS K          ON (K.CONSTRAINT_OBJECT_ID = F.OBJECT_ID)        INNER JOIN SYS.INDEXES I          ON (F.REFERENCED_OBJECT_ID = I.OBJECT_ID              AND F.KEY_INDEX_ID = I.INDEX_ID) WHERE  O1.OBJECT_ID = F.REFERENCED_OBJECT_ID        AND O2.OBJECT_ID = F.PARENT_OBJECT_ID        AND C1.OBJECT_ID = F.REFERENCED_OBJECT_ID        AND C2.OBJECT_ID = F.PARENT_OBJECT_ID        AND C1.COLUMN_ID = K.REFERENCED_COLUMN_ID        AND C2.COLUMN_ID = K.PARENT_COLUMN_ID

SQL Server 2005 - Version 2

This is another version that was contributed by one of our readers Troy Ketsdever.  This uses the INFORMATION_SCHEMA views.

SELECT C.TABLE_CATALOG [PKTABLE_QUALIFIER],        C.TABLE_SCHEMA [PKTABLE_OWNER],        C.TABLE_NAME [PKTABLE_NAME],        KCU.COLUMN_NAME [PKCOLUMN_NAME],        C2.TABLE_CATALOG [FKTABLE_QUALIFIER],        C2.TABLE_SCHEMA [FKTABLE_OWNER],        C2.TABLE_NAME [FKTABLE_NAME],        KCU2.COLUMN_NAME [FKCOLUMN_NAME],        RC.UPDATE_RULE,        RC.DELETE_RULE,        C.CONSTRAINT_NAME [FK_NAME],        C2.CONSTRAINT_NAME [PK_NAME],        CAST(7 AS SMALLINT) [DEFERRABILITY] FROM   INFORMATION_SCHEMA.TABLE_CONSTRAINTS C        INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE KCU          ON C.CONSTRAINT_SCHEMA = KCU.CONSTRAINT_SCHEMA             AND C.CONSTRAINT_NAME = KCU.CONSTRAINT_NAME        INNER JOIN INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS RC          ON C.CONSTRAINT_SCHEMA = RC.CONSTRAINT_SCHEMA             AND C.CONSTRAINT_NAME = RC.CONSTRAINT_NAME        INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS C2          ON RC.UNIQUE_CONSTRAINT_SCHEMA = C2.CONSTRAINT_SCHEMA             AND RC.UNIQUE_CONSTRAINT_NAME = C2.CONSTRAINT_NAME        INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE KCU2          ON C2.CONSTRAINT_SCHEMA = KCU2.CONSTRAINT_SCHEMA             AND C2.CONSTRAINT_NAME = KCU2.CONSTRAINT_NAME             AND KCU.ORDINAL_POSITION = KCU2.ORDINAL_POSITION WHERE  C.CONSTRAINT_TYPE = 'FOREIGN KEY'

SQL Server 2000 Version

With SQL Server 2000 it is not as straight forward as using a simple SQL statement as shown above.  In order to get this to work for SQL Server 2000 an adjustment was made to the sp_fkeys SP to cursor through all of the user tables.  It is not the prettiest method, but it does seem to do the job. 

click here for the SQL 2000 version

Next Steps

• Add these scripts to your toolbox and next time you are doing some investigative work on your severs use these to get an idea of what FKs exist in your databases.
• Take a look at these other tips to help gather information about your server or databases
  • Retrieving SQL Server Database Properties with DATABASEPROPERTYEX
  • Retrieving SQL Server Index Properties with INDEXPROPERTY
  • Auditing your SQL Server database and server permissions

Related Tips: 1 | 2 | 3 | 4 | 5 | 6 | More | Become a paid author

Last Update: 1/5/2007

Share:

Share

Tweet

Free SQL Server Learning Waiting on Waits Let's connect on facebook! Tweet...tweet...tweet... Follow us on twitter

Comments and Feedback:

Monday, October 13, 2008 - 11:24:47 AM - jgonte	Read The Tip
Hi,  Great article! I think there is an error in version 2 of the script where the information of the primary key corresponds to the foreign key and viceversa.

Monday, March 22, 2010 - 4:19:48 PM - phillips_jim	Read The Tip
That may explain why the two scripts produce the same number of output rows with the same columns but return different results.

Thursday, December 01, 2011 - 6:46:36 AM - Atif

Read The Tip

I use my following script for analyzing foreign key constraints in any database   select fkey.name, object_name(fkey.parent_object_id) FKTable,cols.name FKColumn, object_name(fkey.referenced_object_id) PKTable, colsa.name PKColumn, is_disabled, delete_referential_action_desc, update_referential_action_desc ,create_date, modify_date from sys.foreign_key_columns FKeyC inner join sys.foreign_keys fkey on fkeyc.parent_object_id = fkey.parent_object_id and fkeyc.referenced_object_id = fkey.referenced_object_id inner join sys.all_columns cols on fkeyc.parent_object_id = cols.object_id and parent_column_id = cols.column_id inner join sys.all_columns colsa on fkeyc.referenced_object_id = colsa.object_id and parent_column_id = colsa.column_id order by is_disabled desc,delete_referential_action_desc, update_referential_action_desc Hope it would also work fine.

Post a Comment or Question

Keep it clean and stay on the subject or we may delete your comment.
Your email address is not published. Required fields are marked with an asterisk (*)

*Name

*Email

Notify for updates

Comments *** NOTE *** - If you want to include code from SQL Server Management Studio (SSMS) in your post, please copy the code from SSMS and paste the code into a text editor like NotePad before copying the code below to remove the SSMS formatting.
*Enter Code

 

Sponsor Information

Try the free performance monitoring tool from Idera! Time-strapped database professionals use SQL Monitor to look after their servers. Try it online. What grade do you think your SQL Servers get? Find out with a SQL Server Health Check consultant. Get SQL Server Tips Straight from Kevin Kline. Join the over million SQL Server Professionals who get their issues resolved daily. Are you waiting on SQL Server? Learn about these DMV's.