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.

Share the Post:
Heading photo, Metadata.

What is Metadata?

What is metadata? Well, It’s an odd concept to wrap your head around. Metadata is essentially the secondary layer of data that tracks details about the “regular” data. The regular

XDR solutions

The Benefits of Using XDR Solutions

Cybercriminals constantly adapt their strategies, developing newer, more powerful, and intelligent ways to attack your network. Since security professionals must innovate as well, more conventional endpoint detection solutions have evolved

AI is revolutionizing fraud detection

How AI is Revolutionizing Fraud Detection

Artificial intelligence – commonly known as AI – means a form of technology with multiple uses. As a result, it has become extremely valuable to a number of businesses across

AI innovation

Companies Leading AI Innovation in 2023

Artificial intelligence (AI) has been transforming industries and revolutionizing business operations. AI’s potential to enhance efficiency and productivity has become crucial to many businesses. As we move into 2023, several