SQLServerCentral / Article Discussions / Article Discussions by Author / Discuss content posted by Narasimhan Jayachandran / Building Parent-Child Table Tree information / Latest Posts

RE: Building Parent-Child Table Tree information

chandan.kumar — Wed, 11 Feb 2009 21:30:28 GMT

Thnks buddies its grt stuff. it just saved my lot of time.

RE: Building Parent-Child Table Tree information

IN_Sandeep — Mon, 14 Jul 2008 01:56:20 GMT

nice article and very helpful too.Cheers!Sandy.

RE: Building Parent-Child Table Tree information

Anipaul — Sun, 13 Jul 2008 07:30:35 GMT

Nice stuff......

RE: Building Parent-Child Table Tree information

Cheetah — Fri, 11 Jul 2008 01:24:08 GMT

Does somebody perhaps have the code for an dependence tree for all tables in the database. ie Level 1 all tables with no dependencies, Level 2 all tables that depend on Level 1 and so on..I can probably write it myself, but I have a time constraint, need to move data from one db to another and do some key conversions in between. So my order of processing the tables must be right.

RE: Building Parent-Child Table Tree information

wyfccc — Thu, 10 Jul 2008 20:57:01 GMT

Greate!But, in my routine, I often have to find the storeprocedure correlated to a table or column.I have a script for purpose, but it just do it by key search.Anyone have some good idea for it?

RE: Building Parent-Child Table Tree information

timothyawiseman — Thu, 10 Jul 2008 15:08:18 GMT

Very nice article, and very well done.You can achieve similar functionality using sp_msdependencies if you like, but that requires using an undocumented stored procedure and the bit field can be slightly difficult to get right.

RE: Building Parent-Child Table Tree information

Dugi — Thu, 10 Jul 2008 12:29:45 GMT

Interesting ... lot of stuff here and very rich article thnx and no comment!

RE: Building Parent-Child Table Tree information

Sean McDaniel-487404 — Thu, 10 Jul 2008 12:16:59 GMT

Try this. It takes this idea a step further and creates an entire select query based on the base table you provide.Right now if you run it against Northwind it will create the following query for you:/*SELECT * FROM [Employees] WITH (NOLOCK) LEFT JOIN [EmployeeTerritories] WITH (NOLOCK) ON [EmployeeTerritories].EmployeeID=Employees.EmployeeID INNER JOIN [Orders] WITH (NOLOCK) ON [Orders].EmployeeID=Employees.EmployeeID LEFT join [Order Details] WITH (NOLOCK) ON [Order Details].OrderID=Orders.OrderID*//* Script Name: Recursive Table Layout Author: Sean McDaniel Purpose: Will start with table and work it's way back through the foreign keys pointing to it and so forth until it reaches the highest parent Will also determine whether a left join or right join should be used based on whether the child table allows nulls in the column or not*/set nocount onDECLARE @TableName varchar(200), @level int/*------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------PUT IN THE TABLE YOU'D LIKE TO START WITH*/set @TableName='Employees'set @level=1declare @ParentChildTableTree table(ParentTable sysname null,ChildTable sysname null,[Level] int null,Indent varchar(max) null, JOIN_INFO varchar(max));insert into @ParentChildTableTree select null, null, @level, null, 'SELECT * FROM [' + @TableName + '] WITH (NOLOCK)'set @level=@level+1insert into @ParentChildTableTreeselect distinct rkeyid,fkeyid,@level as [Level], @TableName + '->' + convert(varchar(max),object_name(fkeyid)) as Indent, case when b1.isnullable=1 then 'INNER' else 'LEFT' end + ' JOIN [' + b.name + '] WITH (NOLOCK) ON [' + b.name + '].' + b1.name + '=' + c.name + '.' + c1.name from sysforeignkeys a INNER JOIN sysobjects b ON a.fkeyid=b.id AND b.xtype='U' INNER JOIN syscolumns b1 ON a.fkeyid=b1.id AND a.fkey=b1.colid INNER JOIN sysobjects c ON a.rkeyid=c.id AND c.xtype='U' INNER JOIN syscolumns c1 ON a.rkeyid=c1.id AND a.rkey=c1.colidwhere rkeyid = object_id(@TableName)and rkeyid <> fkeyidwhile @@ROWCOUNT >0begin set @level=@level+1 insert into @ParentChildTableTree select distinct rkeyid,fkeyid,@level as [Level], Indent + convert(varchar(max),object_name(fkeyid)) as Indent, case when b1.isnullable=1 then 'INNER' else 'LEFT' end + ' join [' + b.name + '] WITH (NOLOCK) ON [' + b.name + '].' + b1.name + '=' + c.name + '.' + c1.name from sysforeignkeys fk INNER JOIN sysobjects b ON fk.fkeyid=b.id AND b.xtype='U' INNER JOIN syscolumns b1 ON fk.fkeyid=b1.id AND fk.fkey=b1.colid INNER JOIN sysobjects c ON fk.rkeyid=c.id AND c.xtype='U' INNER JOIN syscolumns c1 ON fk.rkeyid=c1.id AND fk.rkey=c1.colid join @ParentChildTableTree pc on fk.rkeyid=ChildTable where rkeyid <> fkeyid and pc.Level = @level -1 and not exists (select * from @ParentChildTableTree b where fk.rkeyid=b.ParentTable and fk.fkeyid=b.ChildTable)endselect --object_name(ParentTable), Object_Name(ChildTable), Level, Indent, space((Level-1) * 10) + JOIN_INFO from @ParentChildTableTree order by Indent

RE: Building Parent-Child Table Tree information

GSquared — Thu, 10 Jul 2008 12:09:41 GMT

You can still end up with an infinite loop with this (well, it will loop till it hits the recursion limit).Table1 has an FK that references Table3Table2 has an FK that references Table1Table3 has an FK that references Table2So long as at least one of these keys doesn't have a Not Null constraint on the column, this data structure is possible. It's most likely to happen in many-to-many-to-many relations.What you're better off doing, if this kind of chain-key relationship is possible in your database, is a self-referent outer-join in the recursive portion of the CTE, with an Is Null in the Where clause, including the Level column in part of the join.

RE: Building Parent-Child Table Tree information

yzhang — Thu, 10 Jul 2008 08:18:15 GMT

I was working on views or codes to get the same information. Thank you for the codes - saved me a lot of time. Nice code, too!

RE: Building Parent-Child Table Tree information

Ron Kunce — Thu, 10 Jul 2008 08:00:40 GMT

Terrific code! I really like it! However it is dependent on a starting table name in which the tree only goes down. What about the parents of the starting table? Could it be written to go both ways to pick up a Person table as being the parent of the SalesPerson and possibly however many parents of Person there might be?

RE: Building Parent-Child Table Tree information

Jay Taylor-604520 — Thu, 10 Jul 2008 07:34:27 GMT

Great stuff. Thanks for doing this.

RE: Building Parent-Child Table Tree information

Atif-ullah Sheikh — Thu, 10 Jul 2008 03:59:25 GMT

Nice Code.I did the same thing with this query... [code] select ccu.table_schema + '.' + ccu.table_name as MTablename, ccu.column_name as Mcolname, ccu1.table_schema + '.' + ccu1.table_name as Tablename, ccu1.column_name as colname ,ccu3.table_schema + '.' + ccu3.table_name as C2Tablename, ccu3.column_name as C2colname ,ccu5.table_schema + '.' + ccu5.table_name as C3Tablename, ccu5.column_name as C3colname ,ccu7.table_schema + '.' + ccu7.table_name as C4Tablename, ccu7.column_name as C4colname ,ccu9.table_schema + '.' + ccu9.table_name as C5Tablename, ccu9.column_name as C5colname ,ccu11.table_schema + '.' + ccu11.table_name as C6Tablename, ccu11.column_name as C6colname from information_schema.constraint_column_usage CCU inner join information_schema.referential_constraints RC on CCU.constraint_name=RC.unique_constraint_name inner join information_schema.constraint_column_usage CCU1 on RC.constraint_name=ccu1.constraint_name Left Outer join information_schema.constraint_column_usage CCU2 on CCU2.Table_Schema + '.' + CCU2.table_name = CCU1.Table_Schema + '.' + CCU1.table_name Left Outer Join information_schema.referential_constraints RC2 on CCU2.constraint_name=RC2.unique_constraint_name Left Outer Join information_schema.constraint_column_usage CCU3 on RC2.constraint_name=ccu3.constraint_name Left Outer join information_schema.constraint_column_usage CCU4 on CCU4.Table_Schema + '.' + CCU4.table_name = CCU3.Table_Schema + '.' + CCU3.table_name Left Outer Join information_schema.referential_constraints RC3 on CCU4.constraint_name=RC3.unique_constraint_name Left Outer Join information_schema.constraint_column_usage CCU5 on RC3.constraint_name=ccu5.constraint_name Left Outer join information_schema.constraint_column_usage CCU6 on CCU6.Table_Schema + '.' + CCU6.table_name = CCU5.Table_Schema + '.' + CCU5.table_name Left Outer Join information_schema.referential_constraints RC4 on CCU6.constraint_name=RC4.unique_constraint_name Left Outer Join information_schema.constraint_column_usage CCU7 on RC4.constraint_name=ccu7.constraint_name Left Outer join information_schema.constraint_column_usage CCU8 on CCU8.Table_Schema + '.' + CCU8.table_name = CCU7.Table_Schema + '.' + CCU7.table_name Left Outer Join information_schema.referential_constraints RC5 on CCU8.constraint_name=RC5.unique_constraint_name Left Outer Join information_schema.constraint_column_usage CCU9 on RC5.constraint_name=ccu9.constraint_name Left Outer join information_schema.constraint_column_usage CCU10 on CCU10.Table_Schema + '.' + CCU10.table_name = CCU9.Table_Schema + '.' + CCU9.table_name Left Outer Join information_schema.referential_constraints RC6 on CCU10.constraint_name=RC6.unique_constraint_name Left Outer Join information_schema.constraint_column_usage CCU11 on RC6.constraint_name=ccu11.constraint_name where ccu.constraint_name not in (select constraint_name from information_schema.referential_constraints) and ccu.table_schema + '.' + ccu.table_name in ('dbo.Portal_users')[/code]The thing which makes the difference is that I have to add the joins manually if I have to add the level to which i need to go to find the childs. Your code is generic in this case. I was just thinking to go into string processing to resolve this issue in my code, but thanks to you, now I will be using your code for my future developments.Thanks once again and NICE CODE...Atif Sheikh

RE: Building Parent-Child Table Tree information

D.Oc — Thu, 10 Jul 2008 02:41:53 GMT

Ain't saying nothing until Joe Celko says it's OK :Dj/k, good article :)

RE: Building Parent-Child Table Tree information

Cheetah — Thu, 10 Jul 2008 02:29:39 GMT

Nice code that may come in handy, esp for writing code to do cascading deletes. Expected a hierarchial tree of dependencies to be display in Management studio, something MS may consider for the future to make the life of DBA's easier.

Building Parent-Child Table Tree information

jnaras — Wed, 09 Jul 2008 22:37:15 GMT

Comments posted to this topic are about the item [B]Building Parent-Child Table Tree information[/B]