with cte as (
select a.CONSTRAINT_SCHEMA,
a.TABLE_NAME,
b.COLUMN_NAME,
d.CONSTRAINT_NAME,
d.UNIQUE_CONSTRAINT_NAME ,
e.ref_table_name
from [INFORMATION_SCHEMA].[TABLE_CONSTRAINTS] a
left join [INFORMATION_SCHEMA].CONSTRAINT_COLUMN_USAGE b on a.CONSTRAINT_NAME=b.CONSTRAINT_NAME
left join [INFORMATION_SCHEMA].REFERENTIAL_CONSTRAINTS d on a.CONSTRAINT_NAME=d.CONSTRAINT_NAME
left join
(
select object_name(i.object_id) as ref_table_name,kc.name as CONSTRAINT_NAME
from sys.indexes i inner join sys.key_constraints KC on i.[object_id]=KC.[parent_object_id]
) e on d.UNIQUE_CONSTRAINT_NAME =e.CONSTRAINT_NAME
where a.CONSTRAINT_TYPE='FOREIGN KEY'
--and a.TABLE_NAME in ('table1','table2')
)
select distinct CONSTRAINT_SCHEMA ,TABLE_NAME, ref_table_name
from cte
--where TABLE_NAME<>ref_table_name
关系层级
1.
with cte as (
select a.CONSTRAINT_SCHEMA,
a.TABLE_NAME,
b.COLUMN_NAME,
d.CONSTRAINT_NAME,
d.UNIQUE_CONSTRAINT_NAME ,
e.ref_table_name
from [INFORMATION_SCHEMA].[TABLE_CONSTRAINTS] a
left join [INFORMATION_SCHEMA].CONSTRAINT_COLUMN_USAGE b on a.CONSTRAINT_NAME=b.CONSTRAINT_NAME
left join [INFORMATION_SCHEMA].REFERENTIAL_CONSTRAINTS d on a.CONSTRAINT_NAME=d.CONSTRAINT_NAME
left join
(
select object_name(i.object_id) as ref_table_name,kc.name as CONSTRAINT_NAME
from sys.indexes i inner join sys.key_constraints KC on i.[object_id]=KC.[parent_object_id]
) e on d.UNIQUE_CONSTRAINT_NAME =e.CONSTRAINT_NAME
where a.CONSTRAINT_TYPE='FOREIGN KEY'
--and a.TABLE_NAME in ('t1','t2')
)
,cte2 as (
select distinct CONSTRAINT_SCHEMA ,TABLE_NAME, ref_table_name
from cte
where TABLE_NAME<>ref_table_name
)
select distinct a.CONSTRAINT_SCHEMA ,a.TABLE_NAME,a.ref_table_name as leve1_table,
b.ref_table_name as leve2_table,
c.ref_table_name as leve3_table,
d.ref_table_name as leve4_table,
e.ref_table_name as leve5_table,
f.ref_table_name as leve6_table,
f2.ref_table_name as leve7_table,
f3.ref_table_name as leve8_table
from cte2 a left join cte2 b on a.ref_table_name=b.TABLE_NAME
left join cte2 c on b.ref_table_name=c.TABLE_NAME
left join cte2 d on c.ref_table_name=d.TABLE_NAME
left join cte2 e on d.ref_table_name=e.TABLE_NAME
left join cte2 f on e.ref_table_name=f.TABLE_NAME
left join cte2 f2 on f.ref_table_name=f2.TABLE_NAME
left join cte2 f3 on f2.ref_table_name=f3.TABLE_NAME
order by 10 desc, 9 desc,8 desc,7 desc,6 desc,5 desc,4 desc,3 desc,2 desc
2.
with cte as (
select a.CONSTRAINT_SCHEMA,
a.TABLE_NAME,
b.COLUMN_NAME,
d.CONSTRAINT_NAME,
d.UNIQUE_CONSTRAINT_NAME ,
e.ref_table_name
from [INFORMATION_SCHEMA].[TABLE_CONSTRAINTS] a
left join [INFORMATION_SCHEMA].CONSTRAINT_COLUMN_USAGE b on a.CONSTRAINT_NAME=b.CONSTRAINT_NAME
left join [INFORMATION_SCHEMA].REFERENTIAL_CONSTRAINTS d on a.CONSTRAINT_NAME=d.CONSTRAINT_NAME
left join
(
select object_name(i.object_id) as ref_table_name,kc.name as CONSTRAINT_NAME
from sys.indexes i inner join sys.key_constraints KC on i.[object_id]=KC.[parent_object_id]
) e on d.UNIQUE_CONSTRAINT_NAME =e.CONSTRAINT_NAME
where a.CONSTRAINT_TYPE='FOREIGN KEY'
--and a.TABLE_NAME in ('table1','table2')
)
,cte2 as (
select distinct CONSTRAINT_SCHEMA ,TABLE_NAME, ref_table_name
from cte
where TABLE_NAME<>ref_table_name
)
,cte3 as (
select a.TABLE_NAME, a.ref_table_name ,1 as level1 from cte2 a
union all
select e.TABLE_NAME,e.ref_table_name,eh.level1+1 as level1
from cte2 e inner join cte3 eh on eh.ref_table_name=e.TABLE_NAME
)
select TABLE_NAME ,ref_table_name , level1 as level1 from cte3 a
order by 3 desc