Introduction
To get an idea about a new database, or to insert records into a database, or to delete records from a database, it is necessary to know the relationships between the tables. An SQL script is provided, which retrieves and shows in an intuitive way the table hierarchy of a database.
Background
SQL Server provides the table sys.foreign_keys, which holds all foreign keys of a database. A foreign key is attached to a table given by the column parent_object_id. One or more columns of this table, reference one ore more columns of the referenced table, which is given by the column referenced_object_id. In the top (or first) level are the tables that do not reference other tables, that means, they do not have any foreign keys attached. At the second level, are tables that reference tables of the top level, that means they have foreign keys, where the parent_object_id refers to the tables of the second level, and the referenced_object_id refers to tables in the first level and so on. Hierarchical relationships are described by the parent-child paradigm, however in this case, it is a little bit confusing, because the parent is given by the referenced_object_id and the child by the parent_object_id. Based on this concept, a recursive query can be written which has as anchor the tables that do not have foreign keys, and which has as recursion, the child tables of the previous level. Tables, that reference themselves, and that do not reference other tables should also be considered as top level tables.
Using the Code
The examples are based on the AdventureWorks database of Microsoft.
Foreign Keys with Columns
The columns used in a foreign key, are given in the table sys.foreign_key_columns. A variable table is created that holds in each row a foreign key name with a comma separated list of the parent columns and a comma separated list of the child columns. To create a comma separated list out of the rows of the child table sys.foreign_key_columns, the STUFF() function together with FOR XML PATH is used as described in this article.
DECLARE @fkcolumns TABLE(name SYSNAME PRIMARY KEY, referencedtable SYSNAME, _
parenttable SYSNAME, referencedcolumns varchar(MAX), parentcolumns varchar(MAX))
INSERT @fkcolumns
SELECT
a.name,
b.name,
c.name,
STUFF((
SELECT ',' + c.name
FROM sys.foreign_key_columns b
INNER JOIN sys.columns c ON b.referenced_object_id = c.object_id
AND b.referenced_column_id = c.column_id
WHERE a.object_id = b.constraint_object_id
FOR XML PATH('')), 1, 1, '') parentcolumns,
STUFF((
SELECT ',' + c.name
FROM sys.foreign_key_columns b
INNER JOIN sys.columns c ON b.parent_object_id = c.object_id
AND b.parent_column_id = c.column_id
WHERE a.object_id = b.constraint_object_id
FOR XML PATH('')), 1, 1, '') childcolumns
FROM sys.foreign_keys a
INNER JOIN sys.tables b ON a.referenced_object_id = b.object_id
INNER JOIN sys.tables c ON a.parent_object_id = c.object_id;
An excerpt of the @fkcolumns table is given here:
The foreign key FK_SalesOrderDetail_SpecialOfferProduct_SpecialOfferIDProductID has two referenced columns and two parent columns.
Parent-child Tables
A foreign key links a parent table with a child table. However, there might be more than one foreign keys, that link the same tables but with different columns. Therefore, a second variable table is created, that holds each pair of parent-child tables only once. With the same string concatenation method, the child columns are semicolon concatenated. The parent columns do not need to be concatenated, because the parent table is referenced always by the same columns which are the primary key columns.
DECLARE @fkrefs TABLE(referencedtable SYSNAME, parenttable SYSNAME, _
referencedcolumns varchar(MAX), parentcolumns varchar(MAX))
INSERT @fkrefs
SELECT *,
(SELECT TOP 1 b.referencedcolumns
FROM @fkcolumns b
WHERE a.referencedtable = b.referencedtable and a.parenttable = b.parenttable),
STUFF((
SELECT ';' + b.parentcolumns
FROM @fkcolumns b
WHERE a.referencedtable = b.referencedtable and a.parenttable = b.parenttable
FOR XML PATH('')), 1, 1, '')
FROM (
SELECT referencedtable, parenttable
FROM @fkcolumns a
GROUP BY referencedtable, parenttable
) a;
An excerpt of the @fkrefs table is given here:
The table CurrencyRate references twice the table Currency, once through the column FromCurrencyCode and once through the column ToCurrencyCode.
Table Tree
Having all parent-child relationships in the variable table @fkrefs, a recursive query is written using the WITH(...) AS SQL statement. Top level tables, are the tables that are nowhere listed as child tables, that means, they are not found in the parenttable column of the @fkrefs table. One exception is the self referenced tables. These are rows in the @fkrefs table that have equal values in columns referencedtable and parenttable. The following SQL statement gives the top level tables:
SELECT a.name
FROM sys.tables a
LEFT JOIN @fkrefs c ON a.name = c.parenttable AND c.referencedtable <> c.parenttable
WHERE c.referencedtable IS NULL
ORDER BY a.name
An excerpt of the top level tables is given here:
Tables participating in a top level cyclic reference are not found by the above query. A top level cyclic reference is a chain of references, where none of the tables participating in the chain, is a descendant of a top level table. For example, the chain A->B->C->A is a top level cyclic reference, whereas the chain A->B->C->D->B is not a top level cyclic reference, because B is a descendant of the top level table A. Therefore, the first cyclic reference (and any of its descentants) will not be included in the tree, whereas the second cyclic reference will be included in the tree. Child tables of the top level tables, are found, by taking the rows of the @fkrefs table that have as referencedtable a top level table. The child table is given by the parenttable column. By adding also the columns treelevel, treepath, referencedcolumns, and parentcolumns, the following query results:
WITH fks(treelevel, treepath, tablename, referencedcolumns, parentcolumns) AS (
SELECT 0,
CAST(a.name AS VARCHAR(MAX)),
a.name,
CAST('' AS VARCHAR(MAX)),
CAST('' AS VARCHAR(MAX))
FROM sys.tables a
LEFT JOIN @fkrefs c ON a.name = c.parenttable AND c.referencedtable <> c.parenttable
WHERE c.referencedtable IS NULL
UNION ALL
SELECT treelevel + 1,
CAST(a.treepath + '_' + b.parenttable AS varchar(MAX)),
b.parenttable,
b.referencedcolumns,
b.parentcolumns
FROM fks a
INNER JOIN @fkrefs b ON a.tablename = b.referencedtable
WHERE treelevel < 10)
SELECT treelevel,
treepath,
REPLICATE('|---- ', treelevel) + tablename tablename,
referencedcolumns,
parentcolumns
FROM fks
ORDER BY treepath;
An excerpt of the foreign keys tree is given here:
The above query limits the tree level to be smaller than 10. If no limit is set, then this query will never end in databases that have self referencing tables, or cyclic references.
History
- Foreign keys tree with level, path and columns indication
