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


ċ
Hierachical Deletion - Ordered Deletion Method 2.sql
(2k)
Andy Hughes,
Jun 15, 2012, 6:50 AM
Comments