December 15, 2011 at 11:14 pm
Hi to all,
i wanted to find all relationship of specified tables(for example i am speciying Table ‘A’
Table ‘A’ has child relationship with Table ‘B’ and Table ‘C’ and Table ‘B’ and Table ‘C’ has again child relationship with Table ‘D’ and Table ‘E’ and also Table ‘A’ and Table ‘C’ has parent relationship with Table ‘G’ and Table ‘H’)
Now i wanto find all tables when i specify Table’A’ please help me with this...
Thanks in Advance
December 15, 2011 at 11:59 pm
manju3606 (12/15/2011)
Hi to all,i wanted to find all relationship of specified tables(for example i am speciying Table ‘A’
Table ‘A’ has child relationship with Table ‘B’ and Table ‘C’ and Table ‘B’ and Table ‘C’ has again child relationship with Table ‘D’ and Table ‘E’ and also Table ‘A’ and Table ‘C’ has parent relationship with Table ‘G’ and Table ‘H’)
Now i wanto find all tables when i specify Table’A’ please help me with this...
Thanks in Advance
Query SYS.Foreign_Keys & SYS.Foreign_Key_Columns to get the desired result. I will try to write if I got some time.
December 16, 2011 at 2:09 am
You mean something like :
This one will fail if you got circular references !
;with cteObjHirarchy
as (
select 1 as MyLevel
, -1 as FK_object_id
, O.object_id as ChildObjectId
, -1 as ParentObjectId
from sys.all_objects O
where O.[type] = 'U'
and not exists ( Select *
from sys.foreign_keys AS FK
where FK.Parent_object_id = O.object_id )
and name not in ('dtproperties')
UNION ALL
select B.MyLevel+1
, FK.object_id
, FK.parent_object_id
, FK.referenced_object_id
from sys.foreign_keys FK
inner JOIN cteObjHirarchy B
on FK.referenced_object_id = B.ChildObjectId
)
select QUOTENAME ( OBJECT_SCHEMA_NAME ( ChildObjectId )) as TheChildSchema
, QUOTENAME ( OBJECT_NAME ( ChildObjectId )) as TheChildName
, QUOTENAME ( OBJECT_SCHEMA_NAME ( ParentObjectId )) as TheParentSchema
, QUOTENAME ( OBJECT_NAME ( ParentObjectId )) as TheParentName
, QUOTENAME ( OBJECT_NAME ( FK_object_id )) as FKName
, *
from cteObjHirarchy
order by MyLevel desc, TheChildSchema, TheChildName
option( MAXRECURSION 15 )
;
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply