Find all relationship of specified

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Find all relationship of specified

Author

Topic

manju3606
Yak Posting Veteran

78 Posts

Posted - 2011-12-16 : 01:05:42

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

Manju

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-16 : 01:22:33

you can use same logic explained here to get child tables of parent

http://visakhm.blogspot.com/2011/11/recursive-delete-from-parent-child.html

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

manju3606
Yak Posting Veteran

78 Posts

Posted - 2011-12-19 : 00:49:52

Hi thanks for reply,

Sorry to say this i am not geting results how would i need. Please can any one help me with this.

Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-19 : 01:04:29

whats the issue ? can you elaborate?
did you apply logic as explained?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

manju3606
Yak Posting Veteran

78 Posts

Posted - 2011-12-19 : 01:54:09

Hi Visakh thanks for reply'

Please see the below link.
http://social.msdn.microsoft.com/Forums/en-US/transactsql/thread/5b6e4507-200a-48b9-9dca-830d20744543

Thanks

Manju

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-19 : 02:45:00

so you're telling you want both ancestors as well as descendants of table you're selecting based on the relationships?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

manju3606
Yak Posting Veteran

78 Posts

Posted - 2011-12-19 : 04:17:59

Hi,
Yes you are right.
For example if i specify table_name 'fk_table1' in my query my query will give result with 3tables.
1.one is 'pk_table1' that is parent table of 'fk_table1'
and 'fk_table11','fk_table12 ' tables are child of 'fk_table1'
but 'pk_table1' has chil relationship with some more tables like 'fk_table2'
'fk_table22' 'fk_table3' so on....
so i want all tables witch has relationship with table 'fk_table' direct or indirect

Thanks

Manju

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-19 : 05:07:17

thats again same logic i posted in link. but you just need to do it twice one for descendants and other for ancestors

something like


;With Ancestors_CTE(Table_Name,Referred_TableName,[Path])
AS
(
select OBJECT_NAME(f.parent_object_id) AS parent,OBJECT_NAME(f.referenced_object_id) as refrenced,
CAST(OBJECT_NAME(f.referenced_object_id)+ '/' + OBJECT_NAME(f.parent_object_id) AS varchar(max))
from sys.foreign_keys f
where  OBJECT_NAME(f.referenced_object_id)=<your table name>
UNION ALL
SELECT OBJECT_NAME(f.parent_object_id) AS parent,OBJECT_NAME(f.referenced_object_id) as refrenced,
CAST(r.[Path] + '/' + OBJECT_NAME(f.referenced_object_id) AS varchar(max))
from sys.foreign_keys f
join Ancestors_CTE r
ON r.Referred_TableName =OBJECT_NAME(f.parent_object_id)
),
Children_CTE(Table_Name,Referred_TableName,[Path])
AS
(
select OBJECT_NAME(f.parent_object_id) AS parent,OBJECT_NAME(f.referenced_object_id) as refrenced,
CAST(OBJECT_NAME(f.referenced_object_id)+ '/' + OBJECT_NAME(f.parent_object_id) AS varchar(max))
from sys.foreign_keys f
where  OBJECT_NAME(f.parent_object_id)=<your table name>
UNION ALL
SELECT OBJECT_NAME(f.parent_object_id) AS parent,OBJECT_NAME(f.referenced_object_id) as refrenced,
CAST(r.[Path] + '/' + OBJECT_NAME(f.referenced_object_id) AS varchar(max))
from sys.foreign_keys f
join Children_CTE r
ON r.Table_Name =OBJECT_NAME(f.referenced_object_id)
)

SELECT Table_Name,Referred_TableName,[Path] FROM  Ancestors_CTE
UNION 
SELECT Table_Name,Referred_TableName,[Path] FROM Children_CTE
OPTION (MAXRECURSION 0)

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

manju3606
Yak Posting Veteran

78 Posts

Posted - 2011-12-19 : 05:30:30

Hi vishak,

i used this logic vishak when i run this query by specifying table 'fk_table1' in the above query i will get table name of parent 'pk_table1' and all child tables of 'fk_table1' but i am not getting child tables of 'pk_table1' witch has 3 child tables
like (fk_table2,fk_table22,fk_table3) please tell me how to get those tables

thanks

Manju

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-19 : 06:37:19

[code]
;With Ancestors_CTE(Table_Name,Referred_TableName,[Path])
AS
(
select OBJECT_NAME(f.parent_object_id) AS parent,OBJECT_NAME(f.referenced_object_id) as refrenced,
CAST(OBJECT_NAME(f.referenced_object_id)+ '/' + OBJECT_NAME(f.parent_object_id) AS varchar(max))
from sys.foreign_keys f
where OBJECT_NAME(f.referenced_object_id)=<your table name>
UNION ALL
SELECT OBJECT_NAME(f.parent_object_id) AS parent,OBJECT_NAME(f.referenced_object_id) as refrenced,
CAST(r.[Path] + '/' + OBJECT_NAME(f.referenced_object_id) AS varchar(max))
from sys.foreign_keys f
join Ancestors_CTE r
ON r.Referred_TableName =OBJECT_NAME(f.parent_object_id)
),
Children_CTE(Table_Name,Referred_TableName,[Path])
AS
(
select Table_Name,Referred_TableName,[Path]
from Ancestors_CTE

UNION ALL

SELECT OBJECT_NAME(f.parent_object_id) AS parent,OBJECT_NAME(f.referenced_object_id) as refrenced,
CAST(r.[Path] + '/' + OBJECT_NAME(f.referenced_object_id) AS varchar(max))
from sys.foreign_keys f
join Children_CTE r
ON r.Table_Name =OBJECT_NAME(f.referenced_object_id)
)

SELECT Table_Name,Referred_TableName,[Path] FROM Ancestors_CTE
UNION
SELECT Table_Name,Referred_TableName,[Path] FROM Children_CTE
OPTION (MAXRECURSION 0)[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

manju3606
Yak Posting Veteran

78 Posts

Posted - 2011-12-19 : 06:58:17

Hi vishak,

I run the above query by specifying table name 'fk_table1' i getting childs and subchilds of 'fk_table1' but still i am not getting childs of parent 'pk_table1' witch has 3 more child tables.
If I run the above query by specifying table name 'pk_table1' i am getting all the relationship tables witch has relationship direct or indirect,how i need.
But i am not geting all the relationship tables witch has relationship direct or indirect when i am specifying table name 'fk_table1'.

Thanks

thanks

Manju

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-19 : 10:49:15

quote:
Originally posted by manju3606

Hi vishak,

I run the above query by specifying table name 'fk_table1' i getting childs and subchilds of 'fk_table1' but still i am not getting childs of parent 'pk_table1' witch has 3 more child tables.
If I run the above query by specifying table name 'pk_table1' i am getting all the relationship tables witch has relationship direct or indirect,how i need.
But i am not geting all the relationship tables witch has relationship direct or indirect when i am specifying table name 'fk_table1'.

Thanks

thanks

Manju

just extend it to add one more recursive cte to get parent relationships.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

manju3606
Yak Posting Veteran

78 Posts

Posted - 2011-12-20 : 04:56:29

Hi visakh,

Sorry to say this i add one more recursive cte to get parent relationships but i am not getting results i think i am doing something wrong so please visakh would you tell me how to do that.

Thanks

Manju

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-20 : 06:58:21

post the query used

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

manju3606
Yak Posting Veteran

78 Posts

Posted - 2011-12-21 : 01:24:08

Hi Visakh,

Could you please post the query for me it will really great help for me

Manju

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-21 : 11:59:56

First post what you've tried from my last posted suggestion
I have given enough info for you to extend and adapt it to your requirement
Dont expect any more spoonfed answers!
If you try and face any issues I will help
Otherwise whats the use to getting complete solution like this? what will you gain from it?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

manju3606
Yak Posting Veteran

78 Posts

Posted - 2011-12-22 : 04:25:30

Hi visakh,

Thanks a lot and you are right visakh what will i gain if i am getting complete solution!

I tried to get my requirement from your suggestion and i got what i need.
I am getting all relationship of specified tables from below query could you please see that query and tell me if i am wrong any where in that query and one more thing i am not understanding why you used [path} column in that query could you please tell me that and i am sorry i removed path column in below query .

with Ancestors_cte (Table_Name,Referred_TableName)
as
(
select OBJECT_NAME(f.parent_object_id)as child,OBJECT_NAME(f.referenced_object_id)as parent
from sys.foreign_keys f where OBJECT_NAME(f.referenced_object_id)='ILM_tblArchiving'

UNION ALL

select OBJECT_NAME(f.parent_object_id)as child,OBJECT_NAME(f.referenced_object_id)as parent
from sys.foreign_keys f join Ancestors_cte a on a.Table_Name=OBJECT_NAME(f.referenced_object_id))
,

parent_CTE(Table_Name,Referred_TableName)
AS
(
select Table_Name,Referred_TableName
from Ancestors_cte

UNION ALL

select OBJECT_NAME(f.parent_object_id)as child,OBJECT_NAME(f.referenced_object_id)as parent
from sys.foreign_keys f join parent_CTE p on p.Referred_TableName=OBJECT_NAME(f.parent_object_id))
,

parent_child_CTE(Table_Name,Referred_TableName)
AS
(
select Table_Name,Referred_TableName
from parent_CTE

UNION ALL

select OBJECT_NAME(f.parent_object_id)as child,OBJECT_NAME(f.referenced_object_id)as parent
from sys.foreign_keys f join parent_CTE p on p.Referred_TableName=OBJECT_NAME(f.referenced_object_id))
,

child_subchild_CTE(Table_Name,Referred_TableName)
AS
(
select Table_Name,Referred_TableName
from parent_child_CTE

UNION ALL

select OBJECT_NAME(f.parent_object_id)as child,OBJECT_NAME(f.referenced_object_id)as parent
from sys.foreign_keys f join parent_child_CTE cs on cs.Table_Name=OBJECT_NAME(f.referenced_object_id))
,
sp_table_parent_CTE (Table_Name,Referred_TableName)
as

(
select OBJECT_NAME(f.parent_object_id)as child,OBJECT_NAME(f.referenced_object_id)as parent
from sys.foreign_keys f where OBJECT_NAME(f.parent_object_id)='ILM_tblArchiving'

UNION ALL

select OBJECT_NAME(f.parent_object_id)as child,OBJECT_NAME(f.referenced_object_id)as parent
from sys.foreign_keys f join sp_table_parent_CTE sp on sp.Referred_TableName=OBJECT_NAME(f.parent_object_id))
,
sp_table_parent_child_CTE(Table_Name,Referred_TableName)
AS
(
select Table_Name,Referred_TableName
from sp_table_parent_CTE

UNION ALL

select OBJECT_NAME(f.parent_object_id)as child,OBJECT_NAME(f.referenced_object_id)as parent
from sys.foreign_keys f join sp_table_parent_CTE spcp on spcp.Referred_TableName=OBJECT_NAME(f.referenced_object_id))
,
sp_table_child_CTE (Table_Name,Referred_TableName)
as
(
select Table_Name,Referred_TableName
from sp_table_parent_CTE

UNION ALL

select OBJECT_NAME(f.parent_object_id)as child,OBJECT_NAME(f.referenced_object_id)as parent
from sys.foreign_keys f join sp_table_parent_child_CTE spcp on spcp.Table_Name=OBJECT_NAME(f.referenced_object_id))
,
sp_table_child_parent_CTE (Table_Name,Referred_TableName)
as
(
select Table_Name,Referred_TableName
from sp_table_child_CTE

UNION ALL

select OBJECT_NAME(f.parent_object_id)as child,OBJECT_NAME(f.referenced_object_id)as parent
from sys.foreign_keys f join sp_table_child_CTE spc on spc.Table_Name=OBJECT_NAME(f.parent_object_id))

select * from Ancestors_cte
union
select * from parent_CTE
union
select * from parent_child_CTE
union
select * from child_subchild_CTE
union
select * from sp_table_parent_CTE
union
select * from sp_table_parent_child_CTE
union
select * from sp_table_child_CTE
union
select * from sp_table_child_parent_CTE

OPTION (MAXRECURSION 0)

once again thanks a lot.

Manju

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-22 : 05:47:42

No problem
your attempt is appreciated!
Path is just to show you how hierarchy went. it has no other significance!

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

manju3606
Yak Posting Veteran

78 Posts

Posted - 2011-12-22 : 05:58:42

Hi Visakh,

Ok.Thank you so much.

Manju

manju3606
Yak Posting Veteran

78 Posts

Posted - 2011-12-23 : 06:19:09

Hi visakh,

i am adding below query to get RefLevel of all relationship tables and i am getting results also good.I want to store that result in temp table but i am not understanding how to store that result.could you please help me with this

RefLevel_cte as
(Select Referred_TableName, Table_Name, 1 As RefLevel, Referred_TableName As Top_Level_Parent From Ancestors_cte
Union All
Select Case When Number = 1 Then m.Referred_TableName Else a.Table_Name End,
a.Table_Name,
Case When Number = 1 Then m.RefLevel + 1 Else 1 End,
m.Top_Level_Parent
From RefLevel_cte m
Inner Join Ancestors_cte a On a.Referred_TableName = m.Table_Name
Cross Join (Select 1 As Number Union All Select 2) As Numbers
Where m.Table_Name <> m.Referred_TableName)
--Select m.Referred_TableName, m.Table_Name, m.RefLevel
--Into #Tables
--From RefLevel_cte m
--where m.Top_Level_Parent='pk_table1'
--Order By m.RefLevel asc

Thanks

Manju

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-23 : 06:38:36

before last select ... union staement add below


insert into #yourtemptable
select...
union
...

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources