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)
AS
BEGIN
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(o.name 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)
as
(-- 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(o.name 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
)
DELETE FROM Dubs
WHERE ROWID > 1;
RETURN
END
