Nice code that may come in handy, esp for writing code to do cascading deletes. Expected a hierarchial tree of dependencies to be display in Management studio, something MS may consider for the future to make the life of DBA's easier.

Terrific code! I really like it! However it is dependent on a starting table name in which the tree only goes down. What about the parents of the starting table? Could it be written to go both ways to pick up a Person table as being the parent of the SalesPerson and possibly however many parents of Person there might be?

Ron K.

"Any fool can write code that a computer can understand. Good programmers write code that humans can understand." -- Martin Fowler

You can still end up with an infinite loop with this (well, it will loop till it hits the recursion limit).

Table1 has an FK that references Table3Table2 has an FK that references Table1Table3 has an FK that references Table2

So long as at least one of these keys doesn't have a Not Null constraint on the column, this data structure is possible. It's most likely to happen in many-to-many-to-many relations.

What you're better off doing, if this kind of chain-key relationship is possible in your database, is a self-referent outer-join in the recursive portion of the CTE, with an Is Null in the Where clause, including the Level column in part of the join.

Try this. It takes this idea a step further and creates an entire select query based on the base table you provide.Right now if you run it against Northwind it will create the following query for you:/*SELECT * FROM [Employees] WITH (NOLOCK) LEFT JOIN [EmployeeTerritories] WITH (NOLOCK) ON [EmployeeTerritories].EmployeeID=Employees.EmployeeID INNER JOIN [Orders] WITH (NOLOCK) ON [Orders].EmployeeID=Employees.EmployeeID LEFT join [Order Details] WITH (NOLOCK) ON [Order Details].OrderID=Orders.OrderID*//* Script Name: Recursive Table Layout Author: Sean McDaniel Purpose: Will start with table and work it's way back through the foreign keys pointing to it and so forth until it reaches the highest parent Will also determine whether a left join or right join should be used based on whether the child table allows nulls in the column or not*/set nocount onDECLARE @TableName varchar(200), @level int/*------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------PUT IN THE TABLE YOU'D LIKE TO START WITH*/set @TableName='Employees'