My Business Users/Testers/Developers had a problem while they were preparing test data.  If they would like to do any DML operation (mostly delete) on one or more rows in a table, they had to know all its child tables first, then all its grandchild and grand-grand-child and so on. How can they get all the child information?   SQL Server Management Studio provided all child objects under the “View Dependencies” option, but they needed to click to expand the child to see the grandchild information and expand the grand-child to see the grand-grandchild information and so on. It also did not list the self referencing information: that is a foreign key is referencing the primary key on the same table. From SSMS, they could view only this information. If this was in T-SQL, a developer could build a DML script to show this information.

Information about each foreign key constraint is available in the system catalog view called sys.foreign_keys. Let’s look at an example. Consider the SalesPerson table in AdventureWorks sample database.

SalesPerson table child information:

-- Sales.SalesPerson

            -- SalesOrderHeader                                       -- Level 1

                        -- SalesOrderDetail                             -- Level 2

                        -- SalesOrderHeaderSalesReason       -- Level 2

            -- SalesPersonQuotaHistory                            -- Level 1

            -- SalesTerritoryHistory                                  -- Level 1

            -- Store                                                            -- Level 1

                        -- StoreContact                                   -- Level 2

The SalesPerson table has children (Level 1) and grand-children tables (Level 2).

First, we will see how to get the Level 1 child table from the sys.foreign_keys view using SalesPerson as input.

-- Level 1

select object_name(Referenced_Object_ID) as ParentTable,

       object_name(Parent_Object_ID) as ChildTable,

       schema_name(schema_id) as [Schema]

from sys.foreign_keys

where Referenced_Object_ID = object_id('Sales.SalesPerson')

go

-- Level 2

For Level 2, we need to use all child table output from Level 1.

select object_name(Referenced_Object_ID) as ParentTable,

       object_name(Parent_Object_ID) as ChildTable,

       schema_name(schema_id) as [Schema]

from sys.foreign_keys

where  Referenced_Object_ID in (

object_id('Sales.SalesOrderHeader'),

object_id('Sales.SalesPersonQuotaHistory'),

object_id('Sales.SalesTerritoryHistory'),

object_id('Sales.Store'))

go

-- Level 3

For Level 3, use all child table output from Level 2.

select object_name(Referenced_Object_ID) as ParentTable,

       object_name(Parent_Object_ID) as ChildTable,

       schema_name(schema_id) as [Schema]

from sys.foreign_keys

where  Referenced_Object_ID in (

object_id('Sales.SalesOrderDetail'),

object_id('Sales.SalesOrderHeaderSalesReason'),

object_id('Sales.StoreContact')

)

go

So we need to execute the same query recursively to get all information until we get no rows (Level 3). The following Pseudocode is from BOL. Search for 'Recursive Queries Using Common Table Expressions'.

Pseudocode:

WITH cte_name ( column_name [,...n] )

AS

(

CTE_query_definition –- Anchor member is defined.

UNION ALL

CTE_query_definition –- Recursive member is defined referencing cte_name.

)

Here the Anchor member is our Level 1 info and the Recursive member is from Level 2 to Level n info. We union Level 1 and Level [2... n] to get the desired output. I used the same logic to produce this metadata as follows:

declare @TableName sysname

set @TableName = 'Sales.SalesPerson';

with cteParentChild (Referenced_Object_ID,Parent_Object_ID,

     [Schema_ID],

     [Level])

as

(

select Referenced_Object_ID,Parent_Object_ID,[schema_ID],1 as [Level]

from sys.foreign_keys

where Referenced_Object_ID = object_id(@TableName)

union all

select fk.Referenced_Object_ID,

       fk.Parent_Object_ID,fk.[Schema_ID],

       [Level]+1

from sys.foreign_keys fk join cteParentChild pc on fk.Referenced_Object_ID = pc.Parent_Object_ID

)

select object_name(Referenced_Object_ID) as ParentTable,object_name(Parent_Object_ID) as ChildTable,

schema_name(schema_id) as [Schema],[Level]

from cteParentChild

go

The query works fine for the ‘SalesPerson’ table.  I checked for the 'HumanResources.Employee' table, and the query went into an infinite loop and I referred back to the same information in BOL. I  found out the cause is query returns the same values for both the parent and child columns. Table 'Employee' has a self reference. Column ManagerID (foreign key) is referencing EmployeeID

(primary key) of the same Employee table. So, I changed the query to ignore if parent and child values are the same. But I included the same information (parent and child values are the same) after the recursive query. In this example, Employee table itself having a self-reference. It is also possible to have a self-reference in any of the child tables too. I added that information also after the recursive query.

declare @TableName sysname

set @TableName = 'HumanResources.Employee';

with cteParentChild (Referenced_Object_ID,Parent_Object_ID,[Schema_ID],[Level])

as

(

select Referenced_Object_ID,Parent_Object_ID,[Schema_ID],1 as [Level]

from sys.foreign_keys

where Referenced_Object_ID = object_id(@TableName)

and   Referenced_Object_ID <> Parent_Object_ID

union all

select fk.Referenced_Object_ID,fk.Parent_Object_ID,fk.[Schema_ID],[Level]+1

from sys.foreign_keys fk join cteParentChild pc on fk.Referenced_Object_ID = pc.Parent_Object_ID

where fk.Referenced_Object_ID <> fk.Parent_Object_ID

)

select object_name(Referenced_Object_ID) as ParentTable,

       object_name(Parent_Object_ID) as ChildTable,

       schema_name([Schema_ID]) as [Schema] ,[Level]

from cteParentChild

-- after recursive query

union all

-- Finding Parent (input table) self reference information

select object_name(Referenced_Object_ID) as ParentTable,

       object_name(Parent_Object_ID) as ChildTable,

       schema_name([Schema_ID]),1 as [Level]

from sys.foreign_keys

where Referenced_Object_ID = object_id(@TableName)

and   Referenced_Object_ID = Parent_Object_ID

union all

-- Finding Child tables self reference information

select object_name(fk.Referenced_Object_ID) as ParentTable,object_name(fk.Parent_Object_ID) as  ChildTable,

schema_name(fk.[Schema_ID]),[Level]

from sys.foreign_keys fk join cteParentChild pc on fk.Referenced_Object_ID = pc.Parent_Object_ID

and fk.Referenced_Object_ID = fk.Parent_Object_ID

go

For hierarchical order and indentation, I used the same logic from BOL. Search for "Using a recursive common table expression to display a hierarchical list". Then I put my whole query into a stored procedure called spListParentChildTableTree as follows:

if object_id (N'dbo.spListParentChildTableTree', N'P') is not null

     drop proc dbo.spListParentChildTableTree   

go

create proc spListParentChildTableTree

(@TableName sysname)

as

begin

declare @ParentChildTableTree table

(ParentTable sysname,ChildTable sysname,[Schema] sysname,[Level] int,Indent varchar(max));

with cteParentChild (Referenced_Object_ID,Parent_Object_ID,[Schema_ID],[Level],Indent)

as

(

select Referenced_Object_ID,Parent_Object_ID,[Schema_ID],1 as [Level], 

convert(varchar(max),object_name(Parent_Object_ID)) as Indent

from sys.foreign_keys

where Referenced_Object_ID = object_id(@TableName)

and   Referenced_Object_ID <> Parent_Object_ID

union all

select  fk.Referenced_Object_ID,fk.Parent_Object_ID,fk.[Schema_ID],[Level]+1,

convert(varchar(max),Indent+object_name(fk.Parent_Object_ID)) as Indent

from sys.foreign_keys fk join cteParentChild pc on fk.Referenced_Object_ID = pc.Parent_Object_ID

where fk.Referenced_Object_ID <> fk.Parent_Object_ID

)

insert @ParentChildTableTree

select replicate('-', [Level])+' '+object_name(Referenced_Object_ID) as  ParentTable,

object_name(Parent_Object_ID) as ChildTable, schema_name([Schema_ID]) as [Schema] ,[Level],Indent 

from cteParentChild  

union all

-- Finding Parent (input table) self reference information

select '- '+object_name(Referenced_Object_ID) as ParentTable,object_name(Parent_Object_ID) as  ChildTable,

        schema_name([Schema_ID]) as [Schema],1 as [Level],  convert(varchar(max),object_name(Parent_Object_ID)) as Indent

from sys.foreign_keys

where Referenced_Object_ID = object_id(@TableName)

and   Referenced_Object_ID = Parent_Object_ID

union all

-- Finding Child tables self reference information

select replicate('-', [Level])+' '+object_name(fk.Referenced_Object_ID) as  ParentTable,

object_name(fk.Parent_Object_ID) as ChildTable, schema_name(fk.[Schema_ID]) as  [Schema],[Level],

convert(varchar(max),Indent+object_name(fk.Parent_Object_ID)) as Indent

from sys.foreign_keys fk join cteParentChild pc on fk.Referenced_Object_ID = pc.Parent_Object_ID

and fk.Referenced_Object_ID = fk.Parent_Object_ID

order by indent

select ParentTable,ChildTable,[Schema],[Level] from @ParentChildTableTree

return

end

go

exec spListParentChildTableTree'HumanResources.Employee';

go

Now we can run query for our first example table SalesPerson:

exec spListParentChildTableTree 'Sales.SalesPerson'

go

My Business Users/Testers/Developers are very happy to use this procedure to find the child details immediately without waiting for someone to help them. This procedure helped me a lot while moving data between one environment to another and saved a lot of time too. As a DBA/Developer myself, the procedure is very handy for me to use any CRUD operation in any application/database without knowing the inner details.

Narasimhan Jayachandran.