在数据库操作中，无论是插入数据、查询数据还是删除记录，了解表之间的关系都是至关重要的。本文将介绍如何通过SQL脚本直观地展示数据库表的层级结构。
在SQLServer中，可以通过查询系统表sys.foreign_keys来获取数据库中所有外键的信息。外键关联了两个表，一个表通过parent_object_id列指向另一个表，而这个被指向的表则通过referenced_object_id列给出。在层级结构的最顶层，是没有外键指向的表，即它们不包含任何外键。第二层则是指向顶层表的表，即它们的外键中parent_object_id指向第二层的表，而referenced_object_id指向第一层的表，依此类推。
在这种层级关系中，可以使用递归查询来展示表的层级结构。递归查询的锚点是那些没有外键的表，而递归部分则是前一层的子表。需要注意的是，那些自引用的表，即使没有引用其他表，也应该被视为顶层表。
以下示例基于Microsoft的AdventureWorks数据库。
首先，需要获取外键及其对应的列。这可以通过查询系统表sys.foreign_key_columns来实现。创建了一个变量表@fkcolumns，其中每一行包含一个外键名称，以及父列和子列的逗号分隔列表。为了生成子列的逗号分隔列表，使用了STUFF()函数和FOR XML PATH方法。
DECLARE @fkcolumns TABLE (name SYSNAME PRIMARY KEY, referencedtable SYSNAME, parenttable SYSNAME, referencedcolumns varchar(MAX), parentcolumns varchar(MAX))
INSERT INTO @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, ''),
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, '')
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;
例如，外键FK_SalesOrderDetail_SpecialOfferProduct_SpecialOfferIDProductID有两个引用列和两个父列。
外键将父表与子表联系起来。然而，可能存在多个外键，它们关联的是相同的表，但列不同。因此，创建了第二个变量表@fkrefs，它只包含每对父-子表一次。使用相同的字符串连接方法，子列用分号连接。父列不需要连接，因为父表总是通过相同的列引用，这些列是主键列。
DECLARE @fkrefs TABLE (referencedtable SYSNAME, parenttable SYSNAME, referencedcolumns varchar(MAX), parentcolumns varchar(MAX))
INSERT INTO @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;
例如，表CurrencyRate通过列FromCurrencyCode和ToCurrencyCode两次引用表Currency。
有了所有父-子关系在变量表@fkrefs中，可以使用WITH(...) AS SQL语句编写递归查询。顶层表是那些在@fkrefs表的parenttable列中没有列出作为子表的表，也就是说，它们不是任何表的子表。一个例外是自引用表。这些是@fkrefs表中referencedtable和parenttable列值相等的行。
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;
例如，参与顶层循环引用的表不会被上述查询找到。顶层循环引用是一系列引用链，其中参与链的表都不是顶层表的后代。例如，链A->B->C->A是顶层循环引用，而链A->B->C->D->B不是顶层循环引用，因为B是顶层表A的后代。因此，第一个循环引用（及其后代）将不会被包含在树中，而第二个循环引用将被包含在树中。
顶层表的子表可以通过取@fkrefs表中referencedtable为顶层表的行来找到。子表由parenttable列给出。通过添加列treelevel、treepath、referencedcolumns和parentcolumns，可以得到以下查询结果：
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;