Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Building Parent-Child Table Tree information

By Narasimhan Jayachandran,

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

ParentTable ChildTable Schema Level
- SalesPerson SalesOrderHeader Sales 1
-- SalesOrderHeader SalesOrderDetail Sales 2
-- SalesOrderHeader SalesOrderHeaderSalesReason Sales 2
- SalesPerson SalesPersonQuotaHistory Sales 1
- SalesPerson SalesTerritoryHistory Sales 1
- SalesPerson Store Sales 1
-- Store StoreContact Sales 2

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.

Total article views: 10932 | Views in the last 30 days: 5
 
Related Articles
FORUM

Backup by Schema Level

Backup by Schema Level

SCRIPT

Objects Referenced and Referencing

This script reports objects that are referenced by and that reference a given database object. When ...

FORUM

Can't Drop Schema

Schema References object that doesn't exist

FORUM

Does SQLSERVER2008 offer Object level recovery

SQLSERVER2008 -Object level recovery

FORUM

Object Level Recovery with Litespeed

Object Level Recovery with Litespeed

Tags
t-sql    
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones