Using Metadata to Solve Complex Problems

Using Metadata to Solve Complex Problems

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


About Our Editorial Process

At DevX, we’re dedicated to tech entrepreneurship. Our team closely follows industry shifts, new products, AI breakthroughs, technology trends, and funding announcements. Articles undergo thorough editing to ensure accuracy and clarity, reflecting DevX’s style and supporting entrepreneurs in the tech sphere.

See our full editorial policy.

About Our Journalist