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