以下sql根据它们的关系分隔表.问题在于3000系列下的表格.表是外键的一部分,使用外键.任何人都有一些聪明的递归CTE优选或一个存储过程进行必要的排序？连接到数据库的程序不被视为解决方案.
编辑：我根据第一个解决方案在“答案”中发布了答案
对于任何人转发我自己的“正确”答案,免费的“正确答案”
WITH
AllTables(TableName) AS
(
SELECT OBJECT_SCHEMA_NAME(so.id) +'.'+ OBJECT_NAME(so.id)
FROM dbo.sysobjects so
INNER JOIN sys.all_columns ac ON
so.ID = ac.object_id
WHERE
so.type = 'U'
AND
ac.is_rowguidcol = 1
),Relationships(ReferenceTableName,ReferenceColumnName,TableName,ColumnName) AS
(
SELECT
OBJECT_SCHEMA_NAME (fkey.referenced_object_id) + '.' +
OBJECT_NAME (fkey.referenced_object_id) AS ReferenceTableName,COL_NAME(fcol.referenced_object_id,fcol.referenced_column_id) AS ReferenceColumnName,OBJECT_SCHEMA_NAME (fkey.parent_object_id) + '.' +
OBJECT_NAME(fkey.parent_object_id) AS TableName,COL_NAME(fcol.parent_object_id,fcol.parent_column_id) AS ColumnName
FROM sys.foreign_keys AS fkey
INNER JOIN sys.foreign_key_columns AS fcol ON
fkey.OBJECT_ID = fcol.constraint_object_id
),NotReferencedOrReferencing(TableName) AS
(
SELECT TableName FROM AllTables
EXCEPT
SELECT TableName FROM Relationships
EXCEPT
SELECT ReferenceTableName FROM Relationships
),OnlyReferenced(Tablename) AS
(
SELECT ReferenceTableName FROM Relationships
EXCEPT
SELECT TableName FROM Relationships
),-- These need to be sorted based on theire internal relationships
ReferencedAndReferencing(TableName,ReferenceTableName) AS
(
SELECT r1.Tablename,r2.ReferenceTableName FROM Relationships r1
INNER JOIN Relationships r2
ON r1.TableName = r2.ReferenceTableName
),OnlyReferencing(TableName) AS
(
SELECT Tablename FROM Relationships
EXCEPT
SELECT ReferenceTablename FROM Relationships
)
SELECT TableName,1000 AS Sorting FROM NotReferencedOrReferencing
UNION
SELECT TableName,2000 AS Sorting FROM OnlyReferenced
UNION
SELECT TableName,3000 AS Sorting FROM ReferencedAndReferencing
UNION
SELECT TableName,4000 AS Sorting FROM OnlyReferencing
ORDER BY Sorting
解决方法
我的调整温和调整：这是一个sql-2005和工作在数据库没有“rowguidcol”：
WITH TablesCTE(SchemaName,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
UNION ALL
SELECT
OBJECT_SCHEMA_NAME(so.object_id) AS SchemaName,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
)
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,t.TableName
