How to get list of child tables for a database table?

Question

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?

Answer 1

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

Answer 2

There is no simple generic answer for this, since tables can recursively depend on other tables including self relationships, etc. Your result could be more than simple tree.

Your best way should depend on your db model: if you have tree tables connected, then delete your data from third table first, than second, than third.

...or disable constraints, delete data, enable constraints.

...or change foreign keys to DELETE CASCADE.

It depends on your data model.

Answer 3

This article gives a good idea of how to do what you're asking.

EDIT: I've modified the original query given in the link to:

1. Make the script schema aware
2. Correct the bug noted in the comments below

Not sure why the editor is doing such a poor job of formatting the code block.

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