SQLServer：如何排序按其外键依赖关系排序的表名 [英] SQLServer: How to sort table names ordered by their foreign key dependency

问题描述

以下SQL根据表的关系分离表。问题是根据3000系列排序的表。属于外键一部分并使用外键的表。任何人都有一些聪明的递归CTE优选或一个存储过程做必要的排序？程序与数据库的连接不被视为解决方案。

The following SQL separates tables according to their relationship. The problem is with the tables that sort under the 3000 series. Tables that are part of foreign keys and that use foreign keys. Anyone got some clever recursive CTE preferably or a stored procedure to do the necessary sorting?? Programs connectiong to the database are not considered a solution.

编辑：我在基于第一个解决方案的答案中发布了答案
免费正确答案对任何人转发我自己的 answer！

I posted the answer in the "answers" based on the first solution Free "right answer" to be had for anyone reposting my own "right" answer!

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

推荐答案

感谢NXC的工作解决方案。您可以使用递归CTE来解决问题。

Thank you for a working solution NXC. You put me on the right track to solve the problem using a recursive CTE.

WITH 
  TablesCTE(TableName, TableID, Ordinal) AS
  (
  SELECT 
    OBJECT_SCHEMA_NAME(so.id) +'.'+ OBJECT_NAME(so.id) AS TableName,
    so.id AS TableID,
    0 AS Ordinal
  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
  UNION ALL
  SELECT 
    OBJECT_SCHEMA_NAME(so.id) +'.'+ OBJECT_NAME(so.id) AS TableName,
    so.id AS TableID,
    tt.Ordinal + 1 AS Ordinal
  FROM 
    dbo.sysobjects so 
    INNER JOIN sys.all_columns ac ON so.ID = ac.object_id
    INNER JOIN sys.foreign_keys f 
      ON (f.parent_object_id = so.id AND f.parent_object_id != f.referenced_object_id)
    INNER JOIN TablesCTE tt ON f.referenced_object_id = tt.TableID
  WHERE
    so.type = 'U'
  AND
    ac.is_rowguidcol = 1
)  
SELECT DISTINCT 
  t.Ordinal,
  t.TableName
  FROM TablesCTE t
  INNER JOIN 
    (
    SELECT 
      TableName as TableName,
      Max (Ordinal) as Ordinal
    FROM TablesCTE
    GROUP BY TableName
    ) tt ON (t.TableName = tt.TableName  AND t.Ordinal = tt.Ordinal)
ORDER BY t.Ordinal, t.TableName

对于thoose想知道这是可用的：我将使用它安全地清空数据库，而不会违反任何外键关系。 （通过按降序截断）
我也可以通过以升序填充表格，从另一个数据库中安全地填充数据表。

For thoose wondering what this is useable for: I will use it to safely empty a database without violating any foreign key relations. (By truncating in descending order) I will also be able to safely fill the tables with data from another database by filling the tables in ascending order.

这篇关于SQLServer：如何排序按其外键依赖关系排序的表名的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！