sql-server - 如何获取数据库表的子表列表？

Question

我必须编写一个删除脚本来删除数据库表中的行。然而，该表有很多子表(外键)，这些子表也有子表。

所有关系都有外键，我想使用此信息以正确的顺序获取我必须删除的表列表(首先是叶表，然后是依赖关系图)。

如何以正确的顺序获取给定表的子表列表？

Answer 1

在你的数据库上试试这个，这个脚本一次只会给你一张表的图表。我假设您有一个 Employee 表，但您必须更改第 2 行以检查数据库的特定表:

DECLARE @masterTableName varchar(1000)
SET @masterTableName = 'Employee'

DECLARE @ScannedTables TABLE( Level int, Name varchar(1000) collate Latin1_General_CI_AS )

DECLARE @currentTableCount INT
DECLARE @previousTableCount INT
DECLARE @level INT

SET @currentTableCount = 0
SET @previousTableCount = -1
SET @level = 0

INSERT INTO @ScannedTables VALUES ( @level, @masterTableName )

WHILE @previousTableCount <> @currentTableCount
BEGIN

    SET @previousTableCount = @currentTableCount

    INSERT INTO @ScannedTables

        SELECT DISTINCT
            @level + 1, TC.Table_Name COLLATE Latin1_General_CI_AS 

        FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC
        LEFT JOIN INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS RC ON TC.Constraint_Name = RC.Constraint_Name
        LEFT JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS FTC ON RC.Unique_Constraint_Name = FTC.Constraint_Name

        WHERE TC.CONSTRAINT_TYPE = 'FOREIGN KEY'

        AND FTC.TABLE_NAME COLLATE Latin1_General_CI_AS IN ( SELECT Name FROM @ScannedTables WHERE Level = @level )
        AND TC.Table_Name COLLATE Latin1_General_CI_AS NOT IN ( SELECT Name FROM @ScannedTables )

    SET @level = @level + 1

    SELECT @currentTableCount = COUNT(*) FROM @ScannedTables   
END

SELECT * FROM @ScannedTables