SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

Making Good Use of Sysforeignkeys Table - Part 1: Display table relati

By Jeffrey Yao,

1 Introduction

As a DBA, I am often required by clients to look into a database "created by a previous DBA who has left the company". Among various jobs, one is to document the table relationships in the database because "the original documents are not updated to reflect the current status of the database." What I usually do is to create a tree-format figure in my final report. This serves as a quick reference of the table relationship diagram. A simple example is as follows


To finish my work as easily as possible, my idea is to generate this type of text diagram in Query Analyzer (QA), and then all I need to do is to copy & paste the result to my word document.

2 Implementation

To accomplish my task, I need to use sysforeignkeys table, which exists in every database and contains all the foreign key information. See BOL for the detailed table structure of sysforeignkeys.

The implementation consists of two steps: 

Step 1:  I composed a stored procedure that accepts a table name as an input parameter, then displays all the children tables in a tree structure with the table at the top level. This is the core step

Step 2: I wrote a script to loop through each distinct referenced table ( (object_name(rkeyid) ) in the sysforeignkeys with a cursor, and call the stored procedure composed in Step 1.

Here I will only discuss the stored procedure (SP) in Step 1 because  the script in Step 2 is easy and obvious to implement. There are two key points in the SP, one is about recursion in T-SQL and another is about SQL Server cursor which will be discussed later.

3 Stored Procedure Details

A brief description of the stored procedure algorithm is as follows:
1. When a table has child tables, create a cursor to loop through each child table
2. For each child table, recursively call the stored procedure

create proc usp_DisplayTableRelation
  @table_name varchar(100) -- the procedure will try to find all the child tables for the table @table_name
, @space_len int = -4 output -- for printing position purpose
begin -- sp
    declare @child varchar(100)
    set @space_len = @space_len + 4

    if @table_name is null
    begin -- return
        set @space_len = @space_len - 4
    end -- return 

    if not exists (select * from sysforeignkeys where rkeyid = object_id(@table_name) )
    begin -- leaf level
        if @space_len <= 0
            print @table_name
            print space(@space_len) + '|---' + @table_name

        set @space_len = @space_len - 4
    end -- leaf level
    else -- the @table_name table exists
    begin -- else
        if @space_len <= 0
            print @table_name
            print space(@space_len) + '|---' + @table_name

        if exists ( select * from sysforeignkeys where rkeyid = object_id(@table_name) and rkeyid = fkeyid) -- self referenced
            print space(@space_len) + '|---' + @table_name

        declare curChild cursor local for
        select object_name(fkeyid) as child from sysforeignkeys
        where rkeyid = object_id(@table_name) and rkeyid <> fkeyid 

        open curChild
        fetch next from curChild into @child
        while @@fetch_status = 0
         begin -- cursor loop
             exec usp_DisplayTableRelation @table_name = @child, @space_len = @space_len output 
             fetch next from curChild into @child
          end -- cursor loop

           close curChild
           deallocate curChild
        end --else
        set @space_len = @space_len - 4
end --sp

3.1 Recursion in T-SQL

Generally speaking, recursion is the most concise and code-efficient way to write a function dealing with traversing hierarchy structure. When considering a recursive procedure, we have to consider two questions:
1. What is the exit condition for the procedure?
2. Does each recursive call to the procedure involve a small case of the original problem?

In this case, the answers to the two questions are:

1. When a table has no children (lowest hierarchy level, i.e. a leaf) , in another word, the table is not referenced by any other tables, the SP exits.
2. Yes, each recursive call receives the name of a table at the lower hierarchy level.

Because in SQL 2000, the recursive calls can be nested up to 32 levels, so if the relationship tree has more than 32 levels in depth, an error will occur. This is very rare however. 

3.2 Cursor in T-SQL

In SQL Server 7.0 / 2000, there are two types of cursor, local and global. The global cursor is in the context of connection while the local cursor is specific to the procedure where it is created. So we have to explicitly define the cursor in the SP as local cursor, otherwise, when a recursive call is made, if the cursor is global, an error will occur as there already exists a global cursor with the same name, which was created in the procedure of the outside level.

4 Summary

In this article, we have discussed how to use sysforeignkeys to quickly display the relationship of a table and its child tables.  With very small changes ( just changing the select statement in the cursor definition ), we can also display the relationship of a table and its parent tables. This is a very useful tool for DBAs to quickly review the table relationships in a database.

5 Resources


Total article views: 11617 | Views in the last 30 days: 1
Related Articles

Stored procedure recursive

Stored procedure recursive





Creating a Recursive Report from a Parent Child Dimension

Reporting Services supports recursive hierarchies and Analysis Services supports parent-child dimen...


call a cursor in a procedure

call a cursor in a procedure


Inserting rows Using Cursors in Stored Procedure

Inserting rows Using Cursors in Stored Procedure