I am trying to get all tables recursively who refer the one table , supposing to say "ta" by CTE as belows.
with CTE(Constraint_object_ID, Constraint_column_ID,HierarchyID,
select Constraint_object_ID,Constraint_column_ID ,HierarchyID=1,
from sys.foreign_key_columns FK
inner join sys.columns C1
on FK.referenced_Object_ID=C1.object_ID and FK.referenced_Column_ID=C1.Column_ID
inner join sys.columns C2
on FK.parent_Object_ID=C2.object_ID and FK.parent_Column_ID=C2.Column_ID
select CTE.Constraint_object_ID, CTE.Constraint_column_ID,HierarchyID=HierarchyID+1,
tableName,FKColumn,ParentTable,PKColumn from CTE inner join sys.foreign_key_columns FK
select * from CTE
order by Constraint_object_ID, Constraint_column_ID
While it reports an error like this "The statement terminated. The maximum recursion 100 has been exhausted before statement completion.", I doubt a bit that soemthing wrong in my T-SQL however I did not figure out "where" yet.
View Complete Post