SQL Server 2000：按引用表數排序的表列表

SQL Server 2000 中是否有任何方法可以獲取按依賴項排序的表列表？我需要它在空數據庫中插入訂單。

我無法關閉約束，所以我堅持使用這個解決方案。

謝謝

我認為這個功能是一個很好的起點。原始來源在時間的流沙中消失了，但我從Uri Dimant中找到了它。

通常我不會批量複製連結的內容，但這看起來對每個人都有好處（我編輯了一些錯別字）。

---

前段時間我被要求截斷客戶數據庫中的許多表。因此，所有這些表都具有 FK 和 PK 依賴性。如果您在 Northwind 數據庫中執行 TRUNCATE TABLE Categories 作為範例，則會收到類似的錯誤。

--Cannot truncate table 'Categories' because it is being referenced by a FOREIGN KEY constraint.

所以，沒有大問題，你知道數據庫結構，可以很容易地辨識父子表。但如果你不這樣做呢？我想展示返回按其級別排序的表的函式（遺憾的是，我不記得它是由誰編寫的）。級別 0 表示它是一個引用表，應該最後截斷。

CREATE VIEW VFKs
AS

SELECT
FK.TABLE_SCHEMA AS child_table_schema,
FK.TABLE_NAME AS child_table_name,
PK.TABLE_SCHEMA AS parent_table_schema,
PK.TABLE_NAME AS parent_table_name
FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS AS RC
JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS FK
ON RC.CONSTRAINT_SCHEMA = FK.CONSTRAINT_SCHEMA
AND RC.CONSTRAINT_NAME = FK.CONSTRAINT_NAME
JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS PK
ON RC.UNIQUE_CONSTRAINT_SCHEMA = PK.CONSTRAINT_SCHEMA
AND RC.UNIQUE_CONSTRAINT_NAME = PK.CONSTRAINT_NAME
GO

CREATE FUNCTION dbo.fn_get_tree_of_tables()
RETURNS @tree TABLE
(
table_schema SYSNAME NOT NULL,
table_name SYSNAME NOT NULL,
lvl INT NOT NULL,
PRIMARY KEY(table_schema, table_name)
)
AS
BEGIN

DECLARE @lvl AS INT
SET @lvl = 0

-- top level tables
INSERT INTO @tree
SELECT DISTINCT
parent_table_schema,
parent_table_name,
@lvl
FROM VFKs AS P
WHERE NOT EXISTS
(SELECT *
FROM VFKs AS C
WHERE C.child_table_schema = P.parent_table_schema
AND C.child_table_name = P.parent_table_name)

WHILE @@rowcount > 0
BEGIN

SET @lvl = @lvl + 1

-- non top level tables
INSERT INTO @tree
SELECT DISTINCT
child_table_schema,
child_table_name,
@lvl
FROM VFKs AS C
JOIN @tree AS P
ON P.table_schema = C.parent_table_schema
AND P.table_name = C.parent_table_name
AND lvl = @lvl - 1
WHERE NOT EXISTS
(SELECT *
FROM @tree AS T
WHERE T.table_schema = C.child_table_schema
AND T.table_name = C.child_table_name)

END

SET @lvl = 0

-- tables with no fks
INSERT INTO @tree
SELECT TABLE_SCHEMA, TABLE_NAME, @lvl
FROM INFORMATION_SCHEMA.TABLES AS TB
WHERE NOT EXISTS(
SELECT *
FROM @tree AS TR
WHERE TB.TABLE_SCHEMA = TR.table_schema
AND TB.TABLE_NAME = TR.table_name)
AND
TB.TABLE_TYPE = 'BASE TABLE'

RETURN

END
GO

這是在 Northwind 中呼叫該函式的結果：

SELECT * FROM dbo.fn_get_tree_of_tables() AS T
ORDER BY lvl

table_schema table_name lvl
------------ ----------------------------------- ---
dbo Categories 0
dbo CustomerDemographics 0
dbo Customers 0
dbo Employees 0
dbo Region 0
dbo Shippers 0
dbo Suppliers 0
dbo Products 1
dbo Territories 1
dbo Orders 1
dbo CustomerCustomerDemo 1
dbo Order Details 2
dbo EmployeeTerritories 2

引用自：https://dba.stackexchange.com/questions/27439