在关系数据库中，表之间的关系对于数据模型的构建和规范化至关重要。SQL Server等数据库管理系统允许在没有预先警告的情况下执行许多操作，即使这些操作涉及到不当的表关系。应用程序可能运行良好，但在表关系层面可能存在大量问题。
今天，尝试编写一个SQL查询，以帮助找到关系数据库中每个表的层次结构或生成。
简而言之，一个好的关系数据库意味着：
以下是一个好的关系数据库设计的示例：
如果在考虑最糟糕的情况，那么它可能是这样的：
如果数据库表的数量较少，使用设计图很容易获得所有信息。但是，如果有很多表，甚至有不同的模式，那么将它们全部连接起来就变得困难了。
让开始讨论提到的查询。
首先，需要找到数据库中不同表之间的关系。基本上，这就像是通过外键关系找到依赖关系。为了找到关系，使用了以下查询，详细信息如下：
CREATE TABLE #tblRelation (
ObjectId VARCHAR(100) NOT NULL,
Name VARCHAR(100) NOT NULL,
[Schema] VARCHAR(100) NOT NULL,
[Column] VARCHAR(100) NOT NULL,
FkFromObjectId VARCHAR(100) NOT NULL,
FkFromTbl VARCHAR(100) NOT NULL,
FkFromSchema VARCHAR(100) NOT NULL,
FkFromClm VARCHAR(100) NOT NULL
)
INSERT INTO #tblRelation
SELECT
parent_object_id,
OBJECT_NAME(parent_object_id),
OBJECT_SCHEMA_NAME(parent_object_id),
c.NAME,
referenced_object_id,
OBJECT_NAME(referenced_object_id),
OBJECT_SCHEMA_NAME(referenced_object_id),
cref.NAME
FROM
sys.foreign_key_columns fk
INNER JOIN
sys.columns c
ON
fk.parent_column_id = c.column_id
AND fk.parent_object_id = c.object_id
INNER JOIN
sys.columns cref
ON
fk.referenced_column_id = cref.column_id
AND fk.referenced_object_id = cref.object_id
SELECT * FROM #tblRelation
ORDER BY Name, [Schema], FkFromTbl, FkFromSchema
如果存在任何关系，将看到类似这样的结果：
如果表之间没有关系，那么这个表中将没有实体。
第二步是使用关系找到第一代表。所谓的第一代，是指：
CREATE TABLE #tblDetail (
Name VARCHAR(100) NOT NULL,
[Schema] VARCHAR(100) NOT NULL,
Generation INT NULL
)
INSERT INTO #tblDetail(Name, [Schema], Generation)
(
SELECT
TABLE_NAME,
TABLE_SCHEMA,
(
CASE
WHEN (
SELECT COUNT(*)
FROM #tblRelation
WHERE Name = TABLE_NAME
AND [Schema] = TABLE_SCHEMA) = 0
)
THEN (
SELECT 0
)
WHEN (
SELECT COUNT(*)
FROM #tblRelation
WHERE Name = TABLE_NAME
AND [Schema] = TABLE_SCHEMA) =
(SELECT COUNT(*)
FROM #tblRelation
WHERE Name = TABLE_NAME
AND FkFromTbl = TABLE_NAME
AND [Schema] = TABLE_SCHEMA)
)
THEN (
SELECT 0
)
ELSE (
SELECT NULL
)
END
)
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
AND TABLE_NAME != 'sysdiagrams'
)
SELECT * FROM #tblDetail
WHERE Generation IS NOT NULL
ORDER BY Generation, Name, [Schema]
如所见，不仅比较表名，还比较模式名。这非常重要，因为数据库可能有多个具有相同名称的表，但它们属于不同的模式。现在，如果在数据库中找到任何第一代表，将将其生成设置为“0”。
在选择之后，将得到类似这样的结果：
重要的是要找到至少一个第一代表以继续工作。但是，如果数据库没有任何第一代表，会进行一个小检查：
IF NOT EXISTS (
SELECT * FROM #tblDetail WHERE Generation IS NOT NULL
)
BEGIN
PRINT 'No first generation table found at table relations.';
END
如果没有找到任何第一代表，将收到一条消息。但不用担心，这个过程不会因此而抛出任何错误。它将尽力走到最后。
在前面的选择语句中，不会有任何实体被选中。
查找其他表的生成很简单。
WHILE (
EXISTS (
SELECT * FROM #tblDetail WHERE Generation IS NULL
)
)
BEGIN
DECLARE @tblNewGeneration TABLE (
Name VARCHAR(100) NOT NULL,
[Schema] VARCHAR(100) NOT NULL
)
INSERT INTO @tblNewGeneration
SELECT
Name, [Schema]
FROM
#tblDetail AS dtl
WHERE
Generation IS NULL
AND NOT EXISTS (
SELECT DISTINCT FkFromTbl, FkFromSchema
FROM #tblRelation AS rel
WHERE rel.Name = dtl.Name
AND rel.[Schema] = dtl.[Schema]
EXCEPT
SELECT Name, [Schema]
FROM #tblDetail
WHERE Generation IS NOT NULL
)
IF (
NOT EXISTS (
SELECT * FROM @tblNewGeneration
)
)
BEGIN
PRINT 'Circular flow found at table relations.';
BREAK;
END
DECLARE @crntGeneration INT
SET @crntGeneration = (
SELECT MAX(Generation) FROM #tblDetail WHERE Generation IS NOT NULL
);
UPDATE #tblDetail
SET Generation = @crntGeneration + 1
WHERE [Schema] + '.' + Name IN (
SELECT [Schema] + '.' + Name FROM @tblNewGeneration
)
DELETE FROM @tblNewGeneration;
END
但是，存在关系循环的可能性。因此，在循环内使用检查来打破循环，如果关系中发现任何循环流。
仍然有表需要找到生成。但是，没有人的依赖项可以使用已经找到生成的表来解决。如果遇到这样的情况，将显示一条消息。
如果没有遇到这些情况，这意味着所有表的生成都已填充。将在生成列中看不到任何“NULL”条目。
SELECT * FROM #tblDetail ORDER BY Generation, Name, [Schema]
正如之前提到的，这个查询将尽力在没有错误的情况下到达终点。但是有可能。有时，在过程结束时，可能会在生成列中找到一些“NULL”条目，这意味着数据库关系面临一些问题，如：