Debunking the Myths: Cloud HA and DR common misconceptions

Learn more about SQL Server tools



solving sql server problems for millions of dbas and developers since 2006 join MSSQLTips for free SQL Server tips
































Top SQL Server Tools



































   Got a SQL tip?
            We want to know!

Identify all of your foreign keys in a SQL Server database

MSSQLTips author Greg Robidoux By:   |   Read Comments (3)   |   Related Tips: 1 | 2 | 3 | 4 | 5 | 6 | More > Constraints

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 THEN 0
                                        
ELSE 1
                                      
END),
       
DELETE_RULE CONVERT(SMALLINT,CASE OBJECTPROPERTY(F.OBJECT_ID,'CnstIsDeleteCascade'
                                        
WHEN 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(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



Last Update: 1/5/2007


About the author
MSSQLTips author Greg Robidoux
Greg Robidoux is the President of Edgewood Solutions and a co-founder of MSSQLTips.com.

View all my tips


print tip Print  
Become a paid author





join MSSQLTips for free SQL Server tips     



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
Get free SQL tips:

*Enter Code refresh code


 
Sponsor Information







Copyright (c) 2006-2014 Edgewood Solutions, LLC All rights reserved
privacy | disclaimer | copyright | advertise | about
authors | contribute | feedback | giveaways | free t-shirt | user groups | community | events | first timer?
Some names and products listed are the registered trademarks of their respective owners.