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

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 2

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

Answer 3

Hi, 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