SQL 表层级结构

• 下载源代码 - 2 KB

引言

要了解一个新数据库的结构，或者向数据库插入记录，或者从数据库删除记录，有必要知道表之间的关系。本文提供了一个 SQL 脚本，该脚本以直观的方式检索并显示数据库的表层级结构。

背景

SQL Server 提供了 `sys.foreign_keys` 表，该表存储了数据库中的所有外键。外键连接到由 `parent_object_id` 列指定的表。该表的一个或多个列引用被引用表的一个或多个列，被引用表由 `referenced_object_id` 列指定。顶层（或第一层）是那些不引用其他表的表，也就是说，它们没有连接任何外键。第二层是引用顶层表的表，也就是说，它们有外键，其中 `parent_object_id` 指向第二层的表，而 `referenced_object_id` 指向第一层的表，依此类推。层级关系由父子范式描述，然而在这个例子中，这有点令人困惑，因为父节点由 `referenced_object_id` 指定，而子节点由 `parent_object_id` 指定。基于这个概念，可以编写一个递归查询，其锚点是没有外键的表，其递归部分是前一层的子表。也应该将引用自身且不引用其他表的表视为顶层表。

Using the Code

这些示例基于 Microsoft 的 `AdventureWorks` 数据库。

带列的外键

`sys.foreign_key_columns` 表中存储了外键使用的列。创建了一个变量表，其中每一行都包含一个外键名称，以及一个逗号分隔的父列列表和一个逗号分隔的子列列表。要从子表 `sys.foreign_key_columns` 的行创建逗号分隔列表，使用了 `STUFF()` 函数和 FOR XML PATH，正如 这篇文章中所述。

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;

这里给出 `@fkcolumns` 表的一个摘录

外键 `FK_SalesOrderDetail_SpecialOfferProduct_SpecialOfferIDProductID` 有两个被引用列和两个父列。

父子表

一个外键连接一个父表和一个子表。然而，可能存在多个外键，它们使用不同的列连接相同的表。因此，创建了第二个变量表，该表只存储每对父子表一次。使用相同的字符串连接方法，将子列通过分号连接。父列不需要连接，因为父表始终由相同的列（主键列）引用。

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;    

这里给出 `@fkrefs` 表的一个摘录

表 `CurrencyRate` 通过 `FromCurrencyCode` 列和 `ToCurrencyCode` 列两次引用表 `Currency`。

表树

在变量表 `@fkrefs` 中拥有所有父子关系后，使用 `WITH(...) AS` SQL 语句编写了一个递归查询。顶层表是没有在任何地方被列为子表的表，也就是说，它们没有出现在 `@fkrefs` 表的 `parenttable` 列中。一个例外是自引用表。这些是 `@fkrefs` 表中 `referencedtable` 和 `parenttable` 列具有相同值的行。下面的 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

这里给出顶层表的一个摘录

上面的查询找不到参与顶层循环引用的表。顶层循环引用是引用链，链中的任何表都不是顶层表的后代。例如，链 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;

这里给出外键树的一个摘录

上述查询将树的层级限制在小于 10。如果没有设置限制，那么在包含自引用表或循环引用的数据库中，此查询将永远不会结束。

历史

• 带层级、路径和列指示的外键树