Hierarchical Table Listing

Applicability:

SQL Server 2000: Tested

SQL Server 2005: Tested

SQL Server 2008: Tested

SQL Server 2008R2: Tested

SQL Server 2012: Not Tested

Credits:

Author: Unknown

Date: 7 May 2012

Description

This is another variation on creating a list of tables ordered by relational hierarchy.

This time, it is implemented as a table valued function, which can be utilised to drive the creation of insert/delete statements by ordering the results differently (Insert by Level DESC; Delete by Level ASC).

Two other approaches to this problem can be found on the page describing hierarchical data deletion techniques.

Code

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);

)