Click here to Skip to main content
15,879,844 members
Articles / Database Development / SQL Server / SQL Server 2008R2
Article

SQL Table Hierarchy

Rate me:
Please Sign up or sign in to vote.
4.59/5 (15 votes)
16 Aug 2016CPOL4 min read 29.4K   379   33   4
An SQL script to see the table hierarchy in a database.

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.

SQL
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:

Variable table @fkcolumns

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.

SQL
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:

Variable table @fkrefs

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:

SQL
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:

Top level tables

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:

SQL
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:

Foreign keys tree

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

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


Written By
Software Developer (Senior) Unisystems
Greece Greece
This member has not yet provided a Biography. Assume it's interesting and varied, and probably something to do with programming.

Comments and Discussions

 
GeneralMy vote of 5 Pin
jjcarrerae10-May-22 3:25
jjcarrerae10-May-22 3:25 
QuestionUndeclared Foreign Keys Pin
Jalapeno Bob6-Oct-16 9:00
professionalJalapeno Bob6-Oct-16 9:00 
Many of the tables I work with have undeclared foreign keys (Hey, don't blame me. I inherited this database. Sigh | :sigh: ) - a column that contains a value that "just happens" to be the key in another table. With luck, the name of the column will be the same in both tables, or at least similar enough to allow you the make the intuitive link on visual inspection.

How about a script to catch and link these? Or at least those with the same column names? It seems simple, but it's not. Consider two records:

Table: Identity
Record_ID (int); Idiot_ID (varchar holding a name); other fields

Table: Orders
Record_ID (int); Idiot_ID (int); other fields

where the value in Identity.Record_ID is "simply used" in Orders.Idiot_ID to identify who gave the order. Now, consider this in a database with many (It seams like hundreds, but it is not) tables. This can be lots of fun... Frown | :(
__________________
Lord, grant me the serenity to accept that there are some things I just can’t keep up with, the determination to keep up with the things I must keep up with, and the wisdom to find a good RSS feed from someone who keeps up with what I’d like to, but just don’t have the damn bandwidth to handle right now.
© 2009, Rex Hammock


QuestionHierarchy Pin
Aless Alessio17-Aug-16 0:58
Aless Alessio17-Aug-16 0:58 
AnswerRe: Hierarchy Pin
Alexandros Pappas19-Sep-16 2:34
professionalAlexandros Pappas19-Sep-16 2:34 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.