;with object_cte(tblid,tblname,rtblid,rtblname,level)
as
(
select distinct o.object_id as tblid,OBJECT_NAME(o.object_id) as tblname,cast(null as int),cast(null as sysname),
0 as level
from sys.objects o
inner join sys.foreign_keys f
on f.parent_object_id = o.object_id
where o.is_ms_shipped=0
and o.type='u'
union all
select t.object_id as tblid,OBJECT_NAME(t.object_id) as tblname,o.tblid,o.tblname,o.level + 1
from object_cte o
inner join sys.foreign_keys f
on f.parent_object_id = o.tblid
join sys.objects t
on t.object_id = f.referenced_object_id
where t.is_ms_shipped=0
and t.type='u'
)
select * from
(
select row_Number() over (partition by tblname order by level) as rn,* from object_cte
)t
where rn=1
order by level
No comments:
Post a Comment