View to insert table by dependency

V_DEV_InsertDataDependency

IF  EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'dbo.V_DEV_InsertDataDependency')) 
DROP VIEW dbo.V_DEV_InsertDataDependency
GO




CREATE VIEW dbo.V_DEV_InsertDataDependency 
AS
WITH Fkeys AS 
( 
    SELECT DISTINCT 
         OnTable       = OnTable.name 
        ,AgainstTable  = AgainstTable.name 
    FROM sysforeignkeys fk 
    
    INNER JOIN sysobjects onTable 
        ON fk.fkeyid = onTable.id 
        
    INNER JOIN sysobjects againstTable  
        ON fk.rkeyid = againstTable.id 
        
    WHERE 1=1 
        AND AgainstTable.TYPE = 'U' 
        AND OnTable.TYPE = 'U' 
        -- ignore self joins; they cause an infinite recursion 
        AND OnTable.Name <> AgainstTable.Name 
)
,MyData AS 
( 
    SELECT 
         OnTable = o.name 
        ,AgainstTable = FKeys.againstTable 
    FROM sys.objects AS o 
    
    LEFT JOIN FKeys 
		ON o.name = FKeys.onTable 
		
    WHERE (1=1) 
        AND o.type = 'U' 
        AND o.name NOT LIKE 'sys%' 
) 
,MyRecursion AS 
( 
    -- base case 
    SELECT 
         TableName  = OnTable 
        ,Lvl        = 1 
    FROM MyData 
    WHERE 1=1 
    AND AgainstTable IS NULL 
    
    -- recursive case 
    UNION ALL 
    
    SELECT 
         TableName = OnTable 
        ,Lvl       = r.Lvl + 1 
    FROM MyData AS d 
    
	INNER JOIN MyRecursion AS r 
		ON d.AgainstTable = r.TableName 
) 

SELECT TOP 999999999999999999 
     MAX(Lvl) AS Lvl 
    ,TableName 
FROM MyRecursion 

GROUP BY TableName 


ORDER BY lvl, TableName 


GO