Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Building Parent-Child Table Tree information


Building Parent-Child Table Tree information

Author
Message
jnaras
jnaras
Old Hand
Old Hand (334 reputation)Old Hand (334 reputation)Old Hand (334 reputation)Old Hand (334 reputation)Old Hand (334 reputation)Old Hand (334 reputation)Old Hand (334 reputation)Old Hand (334 reputation)

Group: General Forum Members
Points: 334 Visits: 78
Comments posted to this topic are about the item Building Parent-Child Table Tree information



Cheetah
Cheetah
SSC Rookie
SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)

Group: General Forum Members
Points: 30 Visits: 299
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.
D.Oc
D.Oc
Ten Centuries
Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)

Group: General Forum Members
Points: 1021 Visits: 6480
Ain't saying nothing until Joe Celko says it's OK BigGrin

j/k, good article Smile

-------------------------------------------------------------
"It takes 15 minutes to learn the game and a lifetime to master"
"Share your knowledge. It's a way to achieve immortality."

Atif-ullah Sheikh
Atif-ullah Sheikh
Hall of Fame
Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)

Group: General Forum Members
Points: 3373 Visits: 5175
Nice Code.

I did the same thing with this query...



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')






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

----------------------------------------------------------------------------------------------------------------------------------------------------------------------

Sometimes, winning is not an issue but trying.

You can check my BLOG here


Jay Taylor-604520
Jay Taylor-604520
Grasshopper
Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)

Group: General Forum Members
Points: 12 Visits: 127
Great stuff. Thanks for doing this.
Ron Kunce
Ron Kunce
SSC-Addicted
SSC-Addicted (482 reputation)SSC-Addicted (482 reputation)SSC-Addicted (482 reputation)SSC-Addicted (482 reputation)SSC-Addicted (482 reputation)SSC-Addicted (482 reputation)SSC-Addicted (482 reputation)SSC-Addicted (482 reputation)

Group: General Forum Members
Points: 482 Visits: 496
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?

Ron K.

"Any fool can write code that a computer can understand. Good programmers write code that humans can understand." -- Martin Fowler
yzhang
yzhang
Forum Newbie
Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)

Group: General Forum Members
Points: 6 Visits: 161
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!
GSquared
GSquared
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16225 Visits: 9729
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 Table3
Table2 has an FK that references Table1
Table3 has an FK that references Table2

So 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.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Sean McDaniel-487404
Sean McDaniel-487404
Forum Newbie
Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)

Group: General Forum Members
Points: 4 Visits: 8
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 on
DECLARE @TableName varchar(200), @level int
/*
------------------------------------------------------------------------------
------------------------------------------------------------------------------
------------------------------------------------------------------------------
PUT IN THE TABLE YOU'D LIKE TO START WITH
*/
set @TableName='Employees'

set @level=1
declare @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+1
insert into @ParentChildTableTree
select 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.colid
where rkeyid = object_id(@TableName)
and rkeyid <> fkeyid
while @@ROWCOUNT >0
begin
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)
end
select --object_name(ParentTable), Object_Name(ChildTable), Level, Indent,
space((Level-1) * 10) + JOIN_INFO from @ParentChildTableTree order by Indent
Dugi
Dugi
UDP Broadcaster
UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)

Group: General Forum Members
Points: 1460 Visits: 3511
Interesting ... lot of stuff here and very rich article
thnx and no comment!

============================================================
SELECT YOUR PROBLEM FROM SSC.com WHERE PROBLEM DESCRIPTION =
http://www.sqlservercentral.com/articles/Best+Practices/61537/
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search