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

Share the Post:
Share on facebook
Share on twitter
Share on linkedin

Overview

The Latest

homes in the real estate industry

Exploring the Latest Tech Trends Impacting the Real Estate Industry

The real estate industry is changing thanks to the newest technological advancements. These new developments — from blockchain and AI to virtual reality and 3D printing — are poised to change how we buy and sell homes. Real estate brokers, buyers, sellers, wholesale real estate professionals, fix and flippers, and beyond may

man on floor with data

DevX Quick Guide to Data Ingestion

One of the biggest trends of the 21st century is the massive surge in internet usage. With major innovations such as smart technology, social media, and online shopping sites, the internet has become an essential part of everyday life for a large portion of the population. Due to this internet

payment via phone

7 Ways Technology Has Changed Traditional Payments

In today’s digital world, technology has changed how we make payments. From contactless cards to mobile wallets, it’s now easier to pay for goods and services without carrying cash or using a checkbook. This article will look at seven of the most significant ways technology has transformed traditional payment methods.