How to list tables in their dependency order (based on foreign keys)?
This question was originally asked by @PrateekGupta
Background
@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.
Requirement
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...
Answer
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
;