Guest
[Entry]
"This will get you the tables in foreign key order and avoid dropping some of the tables created by SQL Server. The t.Ordinal value will slice the tables into dependency layers.
WITH TablesCTE(SchemaName, TableName, TableID, Ordinal) AS ( SELECT OBJECT_SCHEMA_NAME(so.object_id) AS SchemaName, OBJECT_NAME(so.object_id) AS TableName, so.object_id AS TableID, 0 AS Ordinal FROM sys.objects AS so WHERE so.type = 'U' AND so.is_ms_Shipped = 0 AND OBJECT_NAME(so.object_id) LIKE 'MyPrefix%'
UNION ALL SELECT OBJECT_SCHEMA_NAME(so.object_id) AS SchemaName, OBJECT_NAME(so.object_id) AS TableName, so.object_id AS TableID, tt.Ordinal + 1 AS Ordinal FROM sys.objects AS so INNER JOIN sys.foreign_keys AS f ON f.parent_object_id = so.object_id AND f.parent_object_id != f.referenced_object_id INNER JOIN TablesCTE AS tt ON f.referenced_object_id = tt.TableID WHERE so.type = 'U' AND so.is_ms_Shipped = 0 AND OBJECT_NAME(so.object_id) LIKE 'MyPrefix%' ) SELECT DISTINCT t.Ordinal, t.SchemaName, t.TableName, t.TableID FROM TablesCTE AS t INNER JOIN ( SELECT itt.SchemaName AS SchemaName, itt.TableName AS TableName, itt.TableID AS TableID, Max(itt.Ordinal) AS Ordinal FROM TablesCTE AS itt GROUP BY itt.SchemaName, itt.TableName, itt.TableID ) AS tt ON t.TableID = tt.TableID AND t.Ordinal = tt.Ordinal ORDER BY t.Ordinal DESC, t.TableName ASC"
|