SQLServer：如何排序按其外键依赖性排序的表名

以下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

解决方法

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

（编辑：安卓应用网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!