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?
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
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.
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.
- 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
Last Update: 1/5/2007
About the author
View all my tips