Thursday, April 04, 2013

How To: Sequence SQL Tables based on Dependency

There are many times in which you may need to know the sequence of tables based on dependencies in order to process data without foreign constraint issues and without turning off those constraints.  You'll need a list of tables from the most dependent table to the least dependent table.   The process below provides an example in how to provide such a list.

CREATE FUNCTION dbo.GetTableSequence ()
RETURNS @Temp2 Table
(  Sequence int Primary key Identity
 , ObjectID int
 , SchemaName sysname
 , TableName sysname)
DECLARE @Temp1 Table
(  ForeignObjectID int
 , ForeignSchemaName sysname
 , ForeignTableName sysname
 , ObjectID int
 , SchemaName sysname
 , TableName sysname)

-- Identify Tables with Depedency on another table
Insert into @Temp1 (ForeignObjectID, ForeignSchemaName, ForeignTableName, ObjectID, SchemaName, TableName)
Select Distinct O.Object_ID as ForeignObjectID, Cast(schema_Name (o.Schema_ID) as sysname) as ForeignSchemaName, cast( as sysname) as ForeignTableName,
                o2.Object_ID as ObjectID, Cast(schema_Name (o2.Schema_ID) as sysname) as SchemaName, cast(o2.Name as sysname) as TableName
From sys.foreign_keys f
Join sys.objects o
 on f.referenced_object_id = o.object_id
Join sys.objects o2
    on f.parent_object_id = o2.object_id
Where O.Name <> O2.Name -- Exclude Child-Parent Relationships (These causes infinite loops)
-- Recursively Identify Tables from Most Dependent to least Dependent
with RecursionCTE (ObjectId, SchemaName, TableName)
(-- First Get all tables that are not dependent on any other table. (Table can depend on them but not the other way a round)
SELECT T.ObjectID, T.SchemaName, T.TableName
FROM (Select   Distinct O.Object_ID as ObjectID
    , Cast(schema_Name (o.Schema_ID) as sysname) as SchemaName
    , Cast( as sysname) as TableName
  From sys.objects o
   Join sys.columns c
     on c.object_id = o.object_id
  Where o.type = 'u') as T
LEFT OUTER JOIN sys.foreign_key_columns sfk
   ON sfk.referenced_object_id = T.ObjectID
Where sfk.referenced_object_id is null
union all
--  Now recursively work through all dependencies
select R1.ForeignObjectID, R1.ForeignSchemaName, R1.ForeignTableName
FROM @Temp1 as R1
join RecursionCTE as R2 on R1.TableName = R2.TableName
Insert Into @Temp2(ObjectId, SchemaName, TableName)
 select ObjectID, SchemaName, TableName
  from RecursionCTE
-- Delete Duplicates but keep the very last entry
WITH Dubs(ROWID, RecordID, SchemaName, TableName) AS
SELECT ROW_NUMBER() OVER (PARTITION BY TableName ORDER BY Sequence DESC) as ROWID, Sequence, SchemaName, TableName
FROM @Temp2