如何获取数据库表的子表列表?

2021-09-10 00:00:00 tsql sql-server

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

I have to write a delete script to delete rows form a database table. However the table has a lot of children tables (foreign keys) and those children tables have children tables too.

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

There are foreign keys for all relationships and I'd like to use this info to get the list of tables where I'll have to deletes, in the correct order (leaf tables first and then up the dependency graph).

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

How can I get the list of child tables for a given table in the correct order?

推荐答案

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

try this on your database, this script will only give you the graph for one table at a time. I assume you have an Employee table but you would have to change line 2 to check a specific table of your database:

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

相关文章