How to perform Recursive operation on below query

Question

5.00/5 (1 vote)

See more:

Hello,
Hi,

am using this below query to get parent table of specified table. Now i want to get child table of parent table also. So i want to use Recursive and also i tried but i am not getting result as i need.May be i am doing wrong some where. So please help me with this to get child table of parent

SQL

;With
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)='fk_table1' --and OBJECT_NAME(f.parent_object_id) not IN
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] into #temp FROM Children_CTE
OPTION (MAXRECURSION 0)
select * from #temp
drop table #temp

thanks.

[Edit]added pre tag - amitgajjar[/Edit]

Posted 20-Dec-11 0:32am

K N R

Updated 20-Dec-11 0:57am

AmitGajjar

v4

Add a Solution

Comments

AmitGajjar 20-Dec-11 6:58am

removed unused pre tag.

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Wendelius · Answer 1 · 2011-12-20T11:41:00

You could try to modify this to suit your needs:

SQL

WITH Children (parent_object_id, referenced_object_id, child_table, parent_table, level)
AS (
    SELECT f.parent_object_id, 
           f.referenced_object_id, 
           OBJECT_NAME(f.parent_object_id) AS child_table,
           OBJECT_NAME(f.referenced_object_id) as parent_table, 
           0 AS level
    FROM sys.foreign_keys f
    WHERE OBJECT_NAME(f.referenced_object_id) = 'ROOT TABLE NAME GOES HERE'
    UNION ALL
    SELECT f.parent_object_id, 
           f.referenced_object_id, 
           OBJECT_NAME(f.parent_object_id) AS child_table,
           OBJECT_NAME(f.referenced_object_id) as parent_table, 
           level + 1
    FROM sys.foreign_keys f
    INNER JOIN Children c
    ON c.parent_object_id = f.referenced_object_id
)
SELECT parent_table, 
       child_table,  
       parent_object_id, 
       referenced_object_id, 
       level
FROM Children

Amir Mahfoozi · Answer 2 · 2011-12-20T01:18:00

Solution 1

If it is not important for you ro implement it with CTEs then have a look at this page :

http://stackoverflow.com/questions/3441251/how-to-get-list-of-child-tables-for-a-database-table[^]

It will do what you exactly need.

Hope it helps.

Posted 20-Dec-11 1:18am

Amir Mahfoozi

Comments

Wendelius 21-Dec-11 0:41am

I voted 4 for this for few reasons. The link you provided is good, but since the OP wanted to use recursive CTE, the solution is a bit different. Another point is the performance benefit, CTE is quite better performing than procedural approach.

But as said, a good link :)

Amir Mahfoozi 21-Dec-11 0:42am

Thanks Mika :)