Theory and Practice of Database and Data Analysis (2) – Navigating the table relationships

In the first part of the series, I discussed how to use the Sql Server meta data to find database objects. In this part, I will discuss how to find all the data related to a transaction by navigating the table relationships. Let us suppose that the transaction is a purchase order stored in a table called Orders. Any one that has seen the Northwind database knows that it has a child entity called OrderDetails. In even a modest real world database, the complexity can grow very fast. If we can ship a partial order and put the rest in back order, or we need to ship from multiple warehouses, we could have multiple Shipping records for each Order record and have each ShippingDetails record linked to an OrderDetails record. The customers can return the order, either in full or in part, with and without return authorization number, and the actually return may or may not match the return authorization. The system needs to be flexible enough to capture all the events related to the order. As you can see, a very modest order transaction could easily grow to a dozen of tables. If you are not familiar with the database, how can you find all the related table? If you need to delete a record, how do you delete it clean? If you need to copy a transaction from a production system to the development system, how do you copy the entire set of the data related to the transaction?

Fortunately, in any well-designed database, table relationships can be navigated through the foreign key relationships. In Sql server, we can usually find the information using the sp_help stored procedure. Here we will use INFORMATION_SCHEMA to obtain more refined results to be used in our tools. The TABLE_CONSTRAINTS view contains the primary and foreign keys on a table. The REFERENTIAL_CONSTRAINTS view contains the name of the foreign key on the many side and the name of the unique constraint on the one side. It is possible to navigate the table relationships using these two views. The following query will find all the tables that references the Sales.SalesOrderHeader table in the AdventureWorks database:

select tcf.TABLE_SCHEMA, tcf.TABLE_NAME from INFORMATION_SCHEMA.TABLE_CONSTRAINTS tcp inner join INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS rc on tcp.CONSTRAINT_SCHEMA = rc.UNIQUE_CONSTRAINT_SCHEMA and tcp.CONSTRAINT_NAME = rc.UNIQUE_CONSTRAINT_NAME and tcp.CONSTRAINT_TYPE = 'PRIMARY KEY' inner join INFORMATION_SCHEMA.TABLE_CONSTRAINTS tcf on rc.CONSTRAINT_SCHEMA = tcf.CONSTRAINT_SCHEMA and rc.CONSTRAINT_NAME = tcf.CONSTRAINT_NAME where tcp.TABLE_SCHEMA = 'Sales' and tcp.TABLE_NAME = 'SalesOrderHeader'

Here are the results:

The following query will return all the tables referenced by the Sales.SalesOrderHeader table:

select tcp.TABLE_SCHEMA, tcp.TABLE_NAME from INFORMATION_SCHEMA.TABLE_CONSTRAINTS tcp inner join INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS rc on tcp.CONSTRAINT_SCHEMA = rc.UNIQUE_CONSTRAINT_SCHEMA and tcp.CONSTRAINT_NAME = rc.UNIQUE_CONSTRAINT_NAME and tcp.CONSTRAINT_TYPE = 'PRIMARY KEY' inner join INFORMATION_SCHEMA.TABLE_CONSTRAINTS tcf on rc.CONSTRAINT_SCHEMA = tcf.CONSTRAINT_SCHEMA and rc.CONSTRAINT_NAME = tcf.CONSTRAINT_NAME where tcf.TABLE_SCHEMA = 'Sales' and tcf.TABLE_NAME = 'SalesOrderHeader'

And the results:

The Person.Address table appeared twice because we have two columns referencing the table.

The CONSTRAINT_COLUMN_USAGE view contains the columns in the primary and the foreign keys. By using these 3 views, we can construct a simple tool that drills down into the data. In the next part of this series, we will construct such a tool.

Added by Li Chen 9/26/2011

The KEY_COLUMN_USAGE view also contains the columns in the primary and the foreign keys. I prefer to use this view because it also contains the ORDINAL_POSITION that I can use to match the columns in the primary and the foreign keys when we have composite keys. The following example shows how to match the columns. The last line in the query eliminates the records from a table joining to itself.

declare @tablename varchar(256) declare @schema varchar(256) set @schema = 'Sales' set @tablename = 'SalesOrderHeader' select kcup.TABLE_SCHEMA, kcup.TABLE_NAME, kcuf.TABLE_SCHEMA, kcuf.TABLE_NAME, kcup.ORDINAL_POSITION, kcup.COLUMN_NAME, kcuf.COLUMN_NAME from INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS rc inner join INFORMATION_SCHEMA.KEY_COLUMN_USAGE kcup on kcup.CONSTRAINT_SCHEMA = rc.UNIQUE_CONSTRAINT_SCHEMA and kcup.CONSTRAINT_NAME = rc.UNIQUE_CONSTRAINT_NAME inner join INFORMATION_SCHEMA.KEY_COLUMN_USAGE kcuf on kcuf.CONSTRAINT_SCHEMA = rc.CONSTRAINT_SCHEMA and kcuf.CONSTRAINT_NAME = rc.CONSTRAINT_NAME and kcup.ORDINAL_POSITION = kcuf.ORDINAL_POSITION where kcup.TABLE_SCHEMA = @schema and kcup.TABLE_NAME = @tablename and not (kcuf.TABLE_SCHEMA = @schema and kcuf.TABLE_NAME = @tablename)