Here’s a short stored procedure that could be useful when trying to understand a SQL Server database.
Let’s say that you have a simple schema like the one below. Person has GroupID as a foreign key (Group.GroupID), meaning that a person belongs to a single group. A person can also have multiple contact records, so PersonContact has a foreign key indicating which person the contact is for.
In a simple schema like this, you can quickly see the two relationships. You can also see some of this by looking at the Person table in Management Studio, under Columns and Keys. In the diagram below, we see that GroupID is a foreign key and, based on the naming convention, we infer that the corresponding primary key is in the Group table.
This notation relies on the naming convention used for the FK_Person_Group relationship. Also, while looking at the Person table, we’re unable to see which tables might contain foreign keys that refer to the primary key in the Person table.
Below is a simple stored procedure that dumps out all relationships that a specified table participates in. That is, given a table, it tells you:
- All foreign keys that reference the specified table’s primary key
- All foreign keys in the specified table and where the corresponding primary key is located
-- Given specified table, show all PK/FK relationships. Show: -- 1) All foreign keys that reference this table's primary key -- 2) All foreign keys in this table and which table contains primary key create procedure uKeys ( @TableName varchar(255) ) as begin select tfk.name as PKTable, cfk.name as PKColumn, tpk.name as FKTable, cpk.name as FKColumn from sys.foreign_key_columns fk inner join sys.tables as tpk on fk.parent_object_id=tpk.object_id inner join sys.columns as cpk on fk.parent_object_id=cpk.object_id and fk.parent_column_id=cpk.column_id inner join sys.tables as tfk on fk.referenced_object_id=tfk.object_id inner join sys.columns as cfk on fk.referenced_object_id=cfk.object_id and fk.referenced_column_id=cfk.column_id where (tpk.name = @TableName) or (tfk.name = @TableName) order by PKTable, PKColumn, FKTable, FKColumn end;
With this stored proc in place, you can now do the following in a query window, to ask about the keys for the Person table.
ukeys 'Person'
You’ll get some nice output that shows information about all of the relationships for the Person table.
This is a simple example, but this procedure is considerably more useful when you’re dealing with very large databases, where you have a large number of tables and it’s not easy to see all of the relationships for a particular table.
Addendum: There’s already a built-in stored procedure in SQL Server that does basically the same thing (though perhaps in not as clean a format). sp_helpconstraint will dump out the same sort of information, as shown below.
