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.