find all relationship of specified

Question

Post reply

find all relationship of specified

manju3606

SSC Journeyman

Points: 81
More actions
December 15, 2011 at 11:14 pm

#248241

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

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Divine Flame SSCoach Points: 15943 More actions · Answer 1

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.

Sujeet Singh

Johan Bijnens SSC Guru Points: 134873 More actions · Answer 2

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