Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Identify all of your foreign keys in a SQL Server database


By:   |   Read Comments (4)   |   Related Tips: 1 | 2 | 3 | 4 | 5 | More > Constraints

Attend our free MSSQLTips Webcast - How to Simplify Routine SQL Server Administration Tasks


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 Server 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 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 and later.

SQL Server List Foreign Keys - Version 1

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 List Foreign Keys - 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 script

Next Steps


Last Update:


signup button

next tip button



About the author





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


SQL tips:

*Enter Code refresh code     



Friday, January 30, 2015 - 12:45:59 PM - nmil Back To Top

Thanks for a useful posting!  I was attempting to remove a Table that was referred to by a foreign key, and your script was very helpful in getting me pointed in the right direction.

Nice one!

--

NM


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

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.


Monday, March 22, 2010 - 4:19:48 PM - phillips_jim Back To Top

That may explain why the two scripts produce the same number of output rows with the same columns but return different results.


Monday, October 13, 2008 - 11:24:47 AM - jgonte Back To Top

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.

 


Learn more about SQL Server tools