如何对以下查询执行递归操作 [英] How to perform Recursive operation on below query

问题描述

你好，


正在使用下面的查询来获取指定表的父表.现在我也想获取父表的子表.所以我想使用递归并且我也尝试了但是我没有得到想要的结果.可能是我在某些地方做错了.所以请帮我得到父母的孩子表

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

;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

谢谢.

添加了前置标记-amitgajjar [/编辑]

thanks.

added pre tag - amitgajjar[/Edit]

推荐答案

您可以尝试对其进行修改以适合您的需求:

You could try to modify this to suit your needs:

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

如果您对使用CTE实施它并不重要，请查看此页面:

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

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.

这篇关于如何对以下查询执行递归操作的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！