如何在Oracle中按依赖顺序（基于外键）列出数据表？

核心逻辑 #

按表的外键依赖层级分组：

• 第1组：无外键约束的表（无需依赖其他表数据即可插入）
• 第2组：外键仅引用第1组表的表
• 第3组：外键仅引用第1、2组表的表
• 后续组以此类推

各数据库实现脚本 #

MySQL

WITH RECURSIVE table_deps AS (
    -- 初始组：无外键的表，层级1
    SELECT 
        TABLE_NAME,
        1 AS level
    FROM INFORMATION_SCHEMA.TABLES
    WHERE TABLE_SCHEMA = 'your_database_name'
        AND TABLE_NAME NOT IN (
            SELECT DISTINCT TABLE_NAME
            FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
            WHERE TABLE_SCHEMA = 'your_database_name'
                AND REFERENCED_TABLE_NAME IS NOT NULL
        )
    UNION ALL
    -- 递归查找后续层级表
    SELECT 
        kcu.TABLE_NAME,
        td.level + 1 AS level
    FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE kcu
    JOIN table_deps td ON kcu.REFERENCED_TABLE_NAME = td.TABLE_NAME
    WHERE kcu.TABLE_SCHEMA = 'your_database_name'
        AND kcu.REFERENCED_TABLE_NAME IS NOT NULL
        AND kcu.TABLE_NAME NOT IN (SELECT TABLE_NAME FROM table_deps)
        -- 确保当前表所有外键引用都属于已处理的层级
        AND NOT EXISTS (
            SELECT 1
            FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE kcu2
            WHERE kcu2.TABLE_NAME = kcu.TABLE_NAME
                AND kcu2.TABLE_SCHEMA = 'your_database_name'
                AND kcu2.REFERENCED_TABLE_NAME IS NOT NULL
                AND kcu2.REFERENCED_TABLE_NAME NOT IN (SELECT TABLE_NAME FROM table_deps)
        )
)
SELECT level, GROUP_CONCAT(TABLE_NAME ORDER BY TABLE_NAME) AS tables_in_group
FROM table_deps
GROUP BY level
ORDER BY level;

替换your_database_name为目标数据库名，执行后会按层级输出各组表列表。

PostgreSQL

WITH RECURSIVE table_deps AS (
    -- 初始组：无外键的表，层级1
    SELECT 
        tab.table_name,
        1 AS level
    FROM information_schema.tables tab
    WHERE tab.table_schema = 'public'
        AND NOT EXISTS (
            SELECT 1
            FROM information_schema.table_constraints tc
            JOIN information_schema.key_column_usage kcu 
                ON tc.constraint_name = kcu.constraint_name
            WHERE tc.table_name = tab.table_name
                AND tc.table_schema = tab.table_schema
                AND tc.constraint_type = 'FOREIGN KEY'
        )
    UNION ALL
    -- 递归查找后续层级表
    SELECT 
        kcu.table_name,
        td.level + 1 AS level
    FROM information_schema.key_column_usage kcu
    JOIN table_deps td ON kcu.referenced_table_name = td.table_name
    JOIN information_schema.table_constraints tc 
        ON kcu.constraint_name = tc.constraint_name
    WHERE tc.table_schema = 'public'
        AND tc.constraint_type = 'FOREIGN KEY'
        AND kcu.table_name NOT IN (SELECT table_name FROM table_deps)
        -- 确保当前表所有外键引用都属于已处理的层级
        AND NOT EXISTS (
            SELECT 1
            FROM information_schema.key_column_usage kcu2
            JOIN information_schema.table_constraints tc2 
                ON kcu2.constraint_name = tc2.constraint_name
            WHERE kcu2.table_name = kcu.table_name
                AND tc2.table_schema = 'public'
                AND tc2.constraint_type = 'FOREIGN KEY'
                AND kcu2.referenced_table_name NOT IN (SELECT table_name FROM table_deps)
        )
)
SELECT level, string_agg(table_name, ', ' ORDER BY table_name) AS tables_in_group
FROM table_deps
GROUP BY level
ORDER BY level;

替换public为目标schema名称，执行后按层级输出各组表。

SQL Server

WITH RECURSIVE table_deps AS (
    -- 初始组：无外键的表，层级1
    SELECT 
        t.name AS table_name,
        1 AS level
    FROM sys.tables t
    WHERE NOT EXISTS (
        SELECT 1
        FROM sys.foreign_keys fk
        WHERE fk.parent_object_id = t.object_id
    )
    UNION ALL
    -- 递归查找后续层级表
    SELECT 
        t.name AS table_name,
        td.level + 1 AS level
    FROM sys.tables t
    JOIN sys.foreign_keys fk ON fk.parent_object_id = t.object_id
    JOIN sys.tables rt ON fk.referenced_object_id = rt.object_id
    JOIN table_deps td ON rt.name = td.table_name
    WHERE t.name NOT IN (SELECT table_name FROM table_deps)
        -- 确保当前表所有外键引用都属于已处理的层级
        AND NOT EXISTS (
            SELECT 1
            FROM sys.foreign_keys fk2
            JOIN sys.tables rt2 ON fk2.referenced_object_id = rt2.object_id
            WHERE fk2.parent_object_id = t.object_id
                AND rt2.name NOT IN (SELECT table_name FROM table_deps)
        )
)
SELECT level, STRING_AGG(table_name, ', ') WITHIN GROUP (ORDER BY table_name) AS tables_in_group
FROM table_deps
GROUP BY level
ORDER BY level;

直接在目标数据库上下文执行即可，输出按层级分组的表列表。

注意事项 #

• 若存在循环外键依赖（如A引用B，B引用A），脚本无法自动处理这类表，需手动禁用外键约束，插入数据后再重新启用。
• 执行脚本需要具备访问系统表/视图的权限。
• 迁移时严格按分组顺序执行INSERT操作，先插入靠前组的表数据，再插入后续组，可避免外键约束违规。

内容的提问来源于stack exchange，提问作者Gilberto Simón Barrera