Hierarchical Table Listing

DROP FUNCTION dbo.udf_GetHierarchicalTableList

GO

CREATE FUNCTION dbo.udf_GetHierarchicalTableList ()

RETURNS TABLE

AS

/*

USAGE:

To return a list of tables to drive data deletion

SELECT * FROM dbo.udf_GetHierarchicalTableList() ORDER BY Level DESC

To return a list of tables to drive data insertion

SELECT * FROM dbo.udf_GetHierarchicalTableList() ORDER BY Level ASC

*/

RETURN

(

WITH FK_Tables AS

(

SELECT

s1.name AS from_schema,

o1.Name AS from_table,

s2.name AS to_schema,

o2.Name AS to_table

FROM sys.foreign_keys fk

INNER JOIN sys.objects o1

ON fk.parent_object_id = o1.OBJECT_ID

INNER JOIN sys.schemas s1

ON o1.schema_id = s1.schema_id

INNER JOIN sys.objects o2

ON fk.referenced_object_id = o2.OBJECT_ID

INNER JOIN sys.schemas s2

ON o2.schema_id = s2.schema_id

/*For the purposes of finding dependency hierarchy

we're not worried about self-referencing tables*/

WHERE NOT(s1.name = s2.name AND o1.name = o2.name)

)

,Ordered_Tables AS

(

SELECT

s.name AS SchemaName,

t.name AS TableName,

0 AS Level

FROM

(

SELECT *

FROM sys.tables

WHERE name <> 'sysdiagrams'

) t

INNER JOIN sys.schemas s

ON t.schema_id = s.schema_id

LEFT OUTER JOIN FK_Tables fk

ON s.name = fk.from_schema

AND t.name = fk.from_table

WHERE fk.from_schema IS NULL

UNION ALL

SELECT

fk.from_schema,

fk.from_table,

ot.Level + 1

FROM FK_Tables fk

INNER JOIN Ordered_Tables ot

ON fk.to_schema = ot.SchemaName

AND fk.to_table = ot.TableName

)

SELECT DISTINCT

ot.SchemaName,

ot.TableName,

ot.Level

FROM Ordered_Tables ot

INNER JOIN

(

SELECT

SchemaName,

TableName,

MAX(Level) MaxLevel

FROM Ordered_Tables

GROUP BY

SchemaName,

TableName

) mx

ON ot.SchemaName = mx.SchemaName

AND ot.TableName = mx.TableName

AND mx.MaxLevel = ot.Level

--ORDER BY

-- ot.Level DESC

--OPTION (MAXRECURSION 0);

)