如何按依赖顺序列出表(基于外键)?

2022-01-20 00:00:00 foreign-keys sql-server database-metadata

这个问题最初是由@PrateekGupta 提出的

This question was originally asked by @PrateekGupta

@PrateekGupta 想对多个表执行批量插入操作.
表之间有外键关系.
如果在插入引用的表之前对具有外键的表执行 INSERT 操作，则操作可能会由于违反外键而失败.

@PrateekGupta wanted to perform bulk insert operation on multiple tables.
The tables have foreign key relationships between themselves.
If an INSERT operation is done on a table with a foreign key before the referenced table is being inserted to, the operation might fail due to violation of the foreign key.

根据它们的依赖关系在数据库中生成一个表列表.
没有依赖项(没有外键)的表将排在第一位.
仅在第 1 组表中具有依赖关系的表将排在第 2 位.
仅在第 1 组或第 2 组表中具有依赖关系的表将排在第 3 组.
等等……

Produce a list of tables within a database ordered according to their dependencies.
Tables with no dependencies (no foreign keys) will be 1st.
Tables with dependencies only in the 1st set of tables will be 2nd.
Tables with dependencies only in the 1st or 2nd sets of tables will be 3rd.
and so on...

推荐答案

    example:

    create table t1 (i int primary key,j int unique)
    create table t2 (i int primary key references t1 (i));
    create table t3 (i int,j int,primary key (i,j));
    create table t4 (i int,j int,  foreign key (i,j) references t3 (i,j));
    create table t5 (i int references t1 (i),j int,foreign key (i,j) references t3 (i,j));
    create table t6 (i int references t2 (i));

<小时>

with        cte (lvl,object_id,name)
            as 
            (
                select      1
                           ,object_id
                           ,name

                from        sys.tables

                where       type_desc       = 'USER_TABLE'
                        and is_ms_shipped   = 0

                union all

                select      cte.lvl + 1
                           ,t.object_id
                           ,t.name
                from                    cte

                            join        sys.tables  as t

                            on          exists
                                        (
                                            select      null

                                            from        sys.foreign_keys    as fk

                                            where       fk.parent_object_id     = t.object_id 
                                                    and fk.referenced_object_id = cte.object_id
                                        )

                                    and t.object_id <> cte.object_id
                                    and cte.lvl < 30

                where       t.type_desc     = 'USER_TABLE'      
                        and t.is_ms_shipped = 0
            )


select      name
           ,max (lvl)   as dependency_level

from        cte

group by    name

order by    dependency_level
           ,name
;

相关文章