Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
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

grandparent
    |---parent
        |---child
            |---grandchild
    |---uncle
        |---cousin

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
as
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
        return
    end -- return 

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

        set @space_len = @space_len - 4
        return
    end -- leaf level
    else -- the @table_name table exists
    begin -- else
        if @space_len <= 0
            print @table_name
        else 
            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
        return
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

http://msdn.microsoft.com/library/?url=/library/en-us/dnsqlpro03/html/sp03i8.asp

Total article views: 11608 | Views in the last 30 days: 2
 
Related Articles
FORUM

Stored procedure recursive

Stored procedure recursive

FORUM

Cursor

cursor

BLOG

Creating a Recursive Report from a Parent Child Dimension

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

FORUM

Recursive Join Problem

Recursive Join Problem

FORUM

call a cursor in a procedure

call a cursor in a procedure

 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones