s someone who genuinely likes SQL and relational databases, I get a particular kick out of seeing how database engines use relational tables themselves to store their own metadata. As an example, we are all familiar with the sysobjects table, which contains a row for each object in the database.

Understanding the metadata is of more than theoretical interest. It can be used to solve complex problems. Recently, I was given a database with over a hundred tables to look at. Of course, almost no documentation existed. I quickly needed to chart a path through the database. I wanted to build a list of all the tables, starting from the base tables on down.

The sysreferences table contains a row for every foreign key in the database. I can therefore obtain a list of all tables that don’t have any foreign keys and, hence, serve as the “base” of the database using the following code:

select namefrom sysobjects objwherenot exists(select fkeyid from sysreferences where fkeyid = id) andtype = 'U'

The trick is to find the next level of tables. For the next level, I only need to find any tables that have foreign key references to the tables listed above, and only those tables. This logic loop continues until I have found all the tables.

Here is the code for the whole procedure. (The code in this 10-Minute Solution was written to work equally well with SQL Server 6.5. It therefore does not use any of the INFORMATION_SCHEMA views.)

set nocount oncreate table #build_order(   rank int,   id int,   name char(50) null   )declare @rank int, @notfinished intselect @rank = 0, @notfinished = 1--get tables that have no foreign keyinsert into #build_order(rank,id,name) select @rank,id ,namefrom sysobjects objwherenot exists(select fkeyid from sysreferences where fkeyid = id) andtype = 'U'while (@notfinished <> 0)begin   select @rank = @rank + 1      --get tables that have a foreign key reference to    --tables already in our build order table.   insert into #build_order(rank,id)      select    distinct @rank,sr.fkeyid      from sysreferences sr, #build_order bo      where      sr.rkeyid = bo.id and      bo.rank = @rank - 1              and       not exists --but don't include tables 	             --that also reference other tables not 		         --yet in our build order table.      (select * from sysreferences sr1         where sr1.fkeyid = sr.fkeyid and             sr1.rkeyid not in(select id from #build_order                      where rank <= @rank -1)      )   select @notfinished = @@rowcountendselect rank,object_name(id) as Namefrom #build_orderorder by rank,namedrop table #build_order

In my next article, I begin looking at the metadata accessible through the INFORMATION_SCHEMA views.