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

在您的数据库上尝试此操作,此脚本一次只提供一个表的图形。我假设您有一个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

没有简单的通用答案,因为表可以递归地依赖于其他表,包括自关系等。您的结果可能不仅仅是简单的树。

最好的方法应该取决于您的数据库模型:如果您连接了树表,那么首先从第三个表中删除数据,然后从第二个表中删除数据,再从第三个表中删除数据。

…或禁用约束、删除数据、启用约束。

…或将外键更改为 DELETE CASCADE .

This article 给你一个很好的主意怎么做你要求的。

1. 使脚本模式可识别
2. 更正 下面的评论

with Fkeys as (

    select distinct

         OnTable       = onTableSchema.name + '.' + OnTable.name
        ,AgainstTable  = againstTableSchema.name + '.' + AgainstTable.name 

    from 

        sysforeignkeys fk

        inner join sys.objects onTable 
            on fk.fkeyid = onTable.object_id
        inner join sys.objects againstTable  
            on fk.rkeyid = againstTable.object_id

        inner join sys.schemas onTableSchema 
            on onTable.schema_id = onTableSchema.schema_id

        inner join sys.schemas againstTableSchema 
            on againstTable.schema_id = againstTableSchema.schema_id

    where 1=1
        AND AgainstTable.TYPE = 'U'
        AND OnTable.TYPE = 'U'
        -- ignore self joins; they cause an infinite recursion
        and onTableSchema.name + '.' + OnTable.name <> againstTableSchema.name + '.' + AgainstTable.name
    )

,MyData as (

    select 
         OnTable = s.name + '.' + o.name
        ,AgainstTable = FKeys.againstTable

    from 

        sys.objects o
            inner join sys.schemas s
                on o.schema_id = s.schema_id

        left join FKeys
            on  s.name + '.' + o.name = FKeys.onTable
        left join Fkeys fk2
            on s.name + '.' + o.name = fk2.AgainstTable
                and fk2.OnTable = Fkeys.AgainstTable

    where 1=1
        and o.type = 'U'
        and o.name not like 'sys%'
        and fk2.OnTable is null
    )

,MyRecursion as (

    -- base case
    select 
         TableName    = OnTable
        ,Lvl        = 1
    from
        MyData
    where 1=1
        and AgainstTable is null

    -- recursive case
    union all select
         TableName    = OnTable
        ,Lvl        = r.Lvl + 1
    from 
        MyData d
        inner join MyRecursion r
            on d.AgainstTable = r.TableName
)

select 
     Lvl = max(Lvl)
    ,TableName
    ,strSql = 'delete from [' + tablename + ']'
from 
    MyRecursion
group by
    TableName
order by 
     1 desc
    ,2 desc