如何修改SQL Server查询，按主键约束表优先、外键约束表次之排序

要解决trunc/load管道因外键约束导致的写入失败问题，你需要让被其他表引用的主键表排在前面，依赖其他表的外键表排在后面。以下是两种解决方案：

方案1：简单区分依赖关系（适用于无多层级依赖的场景） #

这个版本会将所有不依赖其他表的表（无外键约束）排在前面，有外键约束的表排在后面：

SELECT 
    '[' + TABLE_SCHEMA + '].[' + TABLE_NAME + ']' AS MyTableWithSchema,
    TABLE_SCHEMA AS MySchema,
    TABLE_NAME AS MyTable
FROM qlm.INFORMATION_SCHEMA.TABLES t
WHERE TABLE_TYPE = 'BASE TABLE'
    AND TABLE_NAME != 'sysdiagrams'
ORDER BY 
    -- 无外键的表优先排序
    CASE WHEN EXISTS (
        SELECT 1 
        FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE kcu
        JOIN INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS rc 
            ON kcu.CONSTRAINT_NAME = rc.CONSTRAINT_NAME
        WHERE kcu.TABLE_SCHEMA = t.TABLE_SCHEMA 
          AND kcu.TABLE_NAME = t.TABLE_NAME
    ) THEN 1 ELSE 0 END ASC,
    TABLE_NAME ASC;

方案2：递归拓扑排序（适用于多层级依赖场景） #

如果你的表存在多层依赖（比如表A依赖表B，表B依赖表C），上述简单方案无法保证层级顺序，此时需要用递归CTE实现拓扑排序，按依赖层级从小到大排序：

WITH TableDependencies AS (
    -- 第一步：获取所有无依赖的表（层级0）
    SELECT 
        QUOTENAME(t.TABLE_SCHEMA) + '.' + QUOTENAME(t.TABLE_NAME) AS MyTableWithSchema,
        t.TABLE_SCHEMA AS MySchema,
        t.TABLE_NAME AS MyTable,
        0 AS DependencyLevel,
        t.TABLE_SCHEMA + '.' + t.TABLE_NAME AS TableFullName
    FROM qlm.INFORMATION_SCHEMA.TABLES t
    WHERE TABLE_TYPE = 'BASE TABLE'
      AND TABLE_NAME != 'sysdiagrams'
      AND NOT EXISTS (
          SELECT 1 
          FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE kcu
          JOIN INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS rc 
              ON kcu.CONSTRAINT_NAME = rc.CONSTRAINT_NAME
          WHERE kcu.TABLE_SCHEMA = t.TABLE_SCHEMA 
            AND kcu.TABLE_NAME = t.TABLE_NAME
      )

    UNION ALL

    -- 递归获取依赖于已排序表的表，层级递增
    SELECT 
        QUOTENAME(t.TABLE_SCHEMA) + '.' + QUOTENAME(t.TABLE_NAME) AS MyTableWithSchema,
        t.TABLE_SCHEMA AS MySchema,
        t.TABLE_NAME AS MyTable,
        td.DependencyLevel + 1 AS DependencyLevel,
        t.TABLE_SCHEMA + '.' + t.TABLE_NAME AS TableFullName
    FROM qlm.INFORMATION_SCHEMA.TABLES t
    JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE kcu
        ON t.TABLE_SCHEMA = kcu.TABLE_SCHEMA 
        AND t.TABLE_NAME = kcu.TABLE_NAME
    JOIN INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS rc 
        ON kcu.CONSTRAINT_NAME = rc.CONSTRAINT_NAME
    JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE ref_kcu
        ON rc.UNIQUE_CONSTRAINT_NAME = ref_kcu.CONSTRAINT_NAME
    JOIN TableDependencies td
        ON ref_kcu.TABLE_SCHEMA + '.' + ref_kcu.TABLE_NAME = td.TableFullName
    WHERE TABLE_TYPE = 'BASE TABLE'
      AND TABLE_NAME != 'sysdiagrams'
      AND NOT EXISTS (
          SELECT 1 
          FROM TableDependencies td2
          WHERE td2.TableFullName = t.TABLE_SCHEMA + '.' + t.TABLE_NAME
      )
)
-- 按依赖层级排序，层级相同则按表名排序
SELECT 
    MyTableWithSchema,
    MySchema,
    MyTable
FROM TableDependencies
ORDER BY 
    DependencyLevel ASC,
    MyTable ASC;

注意事项 #

• 如果存在循环外键依赖（比如表A引用表B，表B引用表A），递归查询会报错，需要先手动移除循环依赖（SQL Server本身不允许这种约束，但个别场景可能存在）。
• 拓扑排序会严格按照依赖关系排列，确保写入时先填充被引用的主键表，再填充依赖它的外键表，彻底避免外键约束导致的写入失败。

内容的提问来源于stack exchange，提问作者Mohammed Khan