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

,

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

Rate

Share

Share

Rate