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
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
