SQL Server：查找表的生成或顺序

目录

介绍

背景

临时表

查找表关系详细信息

查找表生成详细信息

查找第一个生成表

结果

表生成

表关系

不需要的场景

局限性

• SqlServer_TableGeneration.zip - 2.6 KB

介绍

在使用关系数据库的过程中，我找到了表之间的关系。目的是以正确的顺序清理表并从另一个数据库同步数据，这使我找到了一个查找表生成顺序的方案。

背景

我们在这做什么？

• 找到表之间的关系。
• 找到每个表的生成。
  • 查找第一代表（没有外键的表，或者用它自己的列作为外键）。
  • 找到剩余表的生成。

临时表

IF object_id('tempdb..#tblRelation') is not null
    DROP TABLE #tblRelation;
IF object_id('tempdb..#tblDetail') is not null
    DROP TABLE #tblDetail;

/*table relation details*/
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);

/*table generation details*/
CREATE TABLE #tblDetail (Name VARCHAR(100) NOT NULL,
                         [Schema] VARCHAR(100) NOT NULL,
                         Generation INT 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;

查找表生成详细信息

现在是时候使用表关系数据查找表生成了。

查找第一个生成表

什么是第一个生成表？

• 没有外键的表。
• 或者只用它自己的列作为外键。

/*find first generation table*/
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');

结果 表生成

/*table generations*/
SELECT *
    FROM #tblDetail
    ORDER BY Generation, Name, [Schema];

表关系

/*table relations*/
SELECT *
    FROM #tblRelation
    ORDER BY Name, [Schema], FkFromTbl, FkFromSchema;

不需要的场景

我们也可以找到NULL作为生成或订单值。这意味着数据库中存在循环关系，重新定义表关系至关重要。

局限性

解决方案可能因数据库版本而异。我们测试的数据库是

• SQL Server-2008R2 
• SQL Server-2012
• SQL Server-2014
• SQL Server-2016

 

原文地址：https://www.codeproject.com/Tips/5061052/SQL-Server-Find-Table-Generation-or-Order