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 2 Check Table Relation

By Jeffrey Yao,

1. Introduction

When I sometimes have to review the design of a database which has been survived for a long time with various DBAs / developers adding a few tables here, modifying some columns there, and deleting some constraints anywhere, I must say I always try to find as many problems as possible and in a time as short as possible. So I can proudly declare that  this database is no longer worth survival and my employer should be given the contract to rebuild the database for, you know, a big cash. :)  (just kidding, I never risk my reputation.).

Because I want to find out problems ASAP, I like to write small tools to diagnose the database automatically instead of looking at the screen or printouts to locate where problem are. One of the problems is circular reference among tables in a database.

A simplified example about circular reference is as follows:

There are three tables, A, B, C, A is referenced by B, B is referenced by C, while C is referenced by A.
Note: Self reference is not a circular reference.

Though it looks funny that we will ever design a database this way, this is not uncommon in an old database when there are tens, even hundreds of tables evolved gradually with many people involved.

To diagnose circular reference problem, I look to sysforeignkeys table again for help.

2. Implementation

To do a complete diagnosis, I take two steps:

Step 1. Write a stored procedure (SP) usp_ChkCircularRef with a table name as an input parameter, tree the input table as the root of its relationship hierarchy tree which is composed of all dependant tables. If the input table appears during the traversing, at a lower level, it means there exists a loop for circular reference.

Step 2. Compose 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. A referenced table means it has at least one child table.

Step 2 is easy to implement, so I will not cover it just to save time for you and me. I will only discuss SP in Step 1.

In the SP, I used recursion again as I find sysforeignkeys table is really a good place to practice recursion programming.

2.1 Algorithm Description for usp_ChkCircularRef 

1. If not exists a global temp table ##jy_record (tname varchar(255), seq int), create one, and inserts the first record with the input table name and seq column = 1 (only this first record has seq column value = 1, so I can always find the input table name during the recursion)

2. If the input table has no child table, then exit  else create a cursor to loop through each child.

3. Insert the child table name into the ##jy_record table, compare whether the child table name is the same as the input table name

4. If same, there exists a circular reference error, then prints out the circular path with the records in ##jy_record, else recursively call usp_ChkCircularRef with the child table name as the input parameter

2.2 Script

There is one thing I'd like to mention here, a global temporary table is visible to all sessions, while a local temp table is only visible to the current session. In our case, we need a global temp table that can hold all the inserted record done in each recursion session.


create proc usp_ChkCircularRef
	@ParentName varchar(255)
  ,	@HierarchyLevel tinyint = 0 output -- used for dropping temp table at the end, not necessary in real use
  as 
  begin -- sp
      set nocount on
      declare @Child varchar(255), @Parent varchar(255), @Msg varchar(2048)
  
      if not exists (select * from tempdb.information_schema.tables where table_name='##jy_record')
      begin -- tmp table
          create table ##jy_record (tname varchar(255), seq int default 2 ) 
          insert into ##jy_record (tname, seq) values (@ParentName, 1)
      end -- tmp table
  
      if not exists (select * from sysforeignkeys where rkeyid = object_id(@ParentName) )
      begin
          delete from ##jy_record where seq <> 1 -- if at the end there is no circular ref, delete all records
                                                                
  -- except the first record which has the original input table 
          return
      end
  
      select @Parent = tname from ##jy_record where seq = 1
  
      set @HierarchyLevel = @HierarchyLevel + 1
  
      declare curTmp cursor local for
      select object_name(fkeyid) as ChildName	from sysforeignkeys 
      where rkeyid = object_id(@ParentName) and rkeyid <> fkeyid -- rkeyid <> fkeyid to omit self ref
  
      open curTmp
      fetch next from curTmp into @Child
      while @@fetch_status = 0
      begin -- loop
          insert into ##jy_record (tname) values (@Child) -- record the child table
  
          if @Parent = @Child -- circular ref occur
          begin -- error info
              set @HierarchyLevel = @HierarchyLevel - 1 
              set @Msg = ''
              declare curErr cursor local for select tname from ##jy_record -- for printing out the path
              open curErr
              fetch next from curErr into @Child
              while @@fetch_status = 0
              begin --
  compose circular path
                 
  set @Msg = 	@Child  + '->' + @Msg
                 
  fetch next from curErr into @Child
              end --
  compose circular path
              set @Msg = substring(@Msg, 1, len(@msg)-2 )
  -- delete the tailing "->"
              print 'Error! ' + @Parent + ' has a circular reference: ' + @Msg
              delete from ##jy_record where seq <> 1
  -- delete the current circular path for next loop
              close curErr
              deallocate curErr
              return
          end -- error info
      else
          exec usp_ChkCircularRef @ParentName = @Child,
  @HierarchyLevel = @HierarchyLevel output
  
          fetch next from curTmp into @Child 
      end -- loop
  
      close curTmp
      deallocate curTmp
      set @HierarchyLevel = @HierarchyLevel - 1 
      if @HierarchyLevel = 0 
          drop table ##jy_record 
  
      return
  end -- sp

If nothing returns after running the script, it means there is no circular reference relationship in the database, otherwise, the circular paths will be printed out as error messages, such as "Error! A has a circular reference: A -> B -> C -> A"

3. Summary

This 2nd part article is complimentary to my 1st part, actually if there exists a circular reference relationship in a database, when you run my script in the 1st part, you will get an error, "Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32).". But running the script in 2nd part, you will get all the circular reference relationship paths a table may have.

4. Resources

The following is a good article from Michelle A. Pooler

http://www.winnetmag.com/SQLServer/Article/ArticleID/5448/5448html

 

Total article views: 7067 | Views in the last 30 days: 2
 
Related Articles
ARTICLE

Common table expressions and circular references

Finding circular references that are stopping your CTE from working.

FORUM

copy parent records with child records

copy master/child records in same table

ARTICLE

Converting a File with Parent-Child Records in SSIS

Convert a file with multiple record types that have a parent child relationship.

FORUM

Circular reference

Is it valid design for TableA to reference TableB (foreign key) and TableB to reference back to Tabl...

FORUM

Circular Reference

Circular Reference I wrote an editorial on the MCA certification referencing a blog by Simon ...

Tags
advanced querying    
t-sql    
 
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