SQL Server：检测关系数据库中的循环关系

• 下载源代码 - 3.6 KB

引言

在关系数据库中，管理表之间的正确关系一直是一个大问题。它在建模和规范化方面起着重要作用。SQL Server 可以在我们进行不当的表关系时，在没有任何事先警告的情况下，让我们做很多疯狂的事情。应用程序可能运行得很好，但在表关系层面可能存在巨大的问题。

今天我将尝试创建一个 SQL 查询，以帮助查找关系数据库中每个表的表层级结构或代数。

背景

换句话说，一个良好的关系数据库意味着：

• 至少存在一个没有依赖关系的表
• 表关系中不存在任何循环流

这是一个良好的关系数据库设计的示例

如果您正在考虑最糟糕的设计，那么就是这样

如果数据库表的数量很少，那么通过设计图可以轻松获得所有信息。但是，如果您有太多表，甚至有不同的模式，那么很难一次性将它们全部排列出来。

那么，让我们开始进行我之前提到的查询吧。

表关系详情

首先，我们必须找到数据库不同表之间的关系。基本上，这就像使用外键关系查找依赖关系。为了找到关系，我使用了这个查询，详细信息如下：

--------------- Table relations detail =>
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

现在，如果存在任何关系，我们将看到类似这样的内容

如果表之间没有关系，那么这个表将没有实体。

查找第一代表和表模式详情

第二步是使用关系查找第一代表。我所说的第一代是指一个表

• 它没有任何依赖关系或外键
• 或者它依赖于自身，或者应用的外键来自其自身的列

---------- Find first generation tables and schema detail for all table =>
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(
            /*if tbl has no fk, first generation tbl, 0*/
            (SELECT COUNT(*)
                FROM #tblRelation
                WHERE Name = TABLE_NAME AND [Schema] = TABLE_SCHEMA) = 0
            )
            THEN(SELECT 0)
            
            WHEN(
            /*if tbl has fk, but all of them from his own columns, first generation tbl, 0*/
            (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)
            
            /*tbl has fk, from other tbl columns, NULL*/
            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”。

在select语句执行后，我们将看到类似这样的内容

如何知道数据库中是否存在独立表？

找到至少一个第一代表以继续我们的工作非常重要。但如果数据库没有任何第一代表，我们会进行一个小检查

/*no first generation at relations*/
IF NOT EXISTS(SELECT *
                FROM #tblDetail
                WHERE Generation IS NOT NULL)
BEGIN
    PRINT 'No first generation table found at table relations.';
END

如果我们找不到任何第一代表，我们将看到一条消息。但别担心，该过程不会因此抛出任何错误。它将尽力完成。

并且在之前的select语句中不会选择任何实体。

查找其他表的代数

查找其他表的代数很简单。

• 查找代数尚未找到的表“@tblDetail 中 Generation 为 NULL”
• 并且它所有的依赖关系都可以 100% 解析，使用代数已找到的表
• 将其代数设置为currentMaxGeneration + 1;
• 继续，直到所有表的代数都找到。

/*set new generation using olds*/
WHILE(EXISTS(SELECT *
                FROM #tblDetail
                WHERE Generation IS NULL))
BEGIN
    /*find new generation tables*/
    DECLARE @tblNewGeneration TABLE(Name VARCHAR(100) NOT NULL,
                                    [Schema] VARCHAR(100) NOT NULL)                                
    /*Select tables where all of its foreign key table’s generation were found*/                            
    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
        )

    /*no new generation table found from old genertaion tables*/
    IF(NOT EXISTS(SELECT * FROM @tblNewGeneration))
    BEGIN
        PRINT 'Circular flow found at table relations.';
        BREAK;
    END

    /*set generation*/
    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
        )

    /*clean the new generation tbls from temp*/    
    DELETE FROM @tblNewGeneration;
END

但是存在一种可能性，即关系中存在循环流。因此，我们在循环中使用一个检查，如果在关系中发现任何循环流，则中断循环。

我们如何理解关系中是否存在任何循环流？

• 仍有一些表的代数需要找到。
• 但是，使用已找到代数的表，无法解析任何表的依赖关系。

如果我们遇到这种情况，将会显示一条消息。

结果：成功

如果我们没有遇到任何这些情况，则意味着所有表的代数都已填充。我们将在代数列中看不到“NULL”条目。

---- Result =>
SELECT *
    FROM #tblDetail
    ORDER BY Generation, Name, [Schema]

结果：失败

正如我之前提到的，此查询将尽力在没有错误的情况下完成。但存在一些不确定性。有时，在过程结束时，我们可能会在代数列中发现一些“NULL”条目，这意味着数据库关系存在一些问题，例如：

• 找不到第一代表
• 关系中发现循环流

在哪里应用此查询？

我主要写这个是为了自我满足，并在我工作过的一些关系数据库上进行了应用。一些数据库提供了良好的反馈。

我们可以将其应用于：

• 快速查看表层级结构以理解系统
• 打算更改表之间的关系或已更改，现在查找其影响
• 添加或删除表后，查找其影响

限制

• 可能存在一些我尚未遇到的错误。所以，如果您发现任何错误，请告诉我。
• 可能有一些工具可以做到这一点。如果您知道其中任何一个，请在评论中提及。