Tables in Parent-Child order

[pankaj_wolfhunter]

Greetings,
I just wanna know if anyone can tell me how to get all user defined
tables in parent-then-child manner. I mean all the parents should be
listed first and then childs.
I dont think there is any direct way to do this, but i am not able to
form any sort of query to achieve this.
Any help will be greatly appreciated.

TIA

[Erland Sommarskog]

(pa***************@yahoo.co.in) writes:

I just wanna know if anyone can tell me how to get all user defined
tables in parent-then-child manner. I mean all the parents should be
listed first and then childs.
I dont think there is any direct way to do this, but i am not able to
form any sort of query to achieve this.
Any help will be greatly appreciated.

I don't think I really understand what you want. In the general case
tables may have many parents, and it all can get quite messy.

This query first lists all tables that does not have a foreign key,
and then those that have.

select o.name
from sysobjects o
where o.xtype = 'U'
order by case when exists (Select *
from sysforeignkeys f
where o.id = f.fkeyid)
then 1
else 0
end, o.name

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[pankaj_wolfhunter]

thanks for the help. i got what i want. thnnx again

[--CELKO--]

Get a copy of TREES & HIERARCHIES IN SQL and learn how to do it one
simple query.

[pankaj_wolfhunter]

thanx for the reply. i'll see to it.

[Tony Rogerson]

The nested set method you will see is:-

1) Difficult to maintain
2) Does not scale nor perform well out of the lab and in the real
world.

A better method is that developed by Itzik Ben Gan, information here:
http://www.windowsitpro.com/Articles...layTab=Article

--
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"--CELKO--" <jc*******@earthlink.net> wrote in message
news:11*********************@f14g2000cwb.googlegro ups.com...

Get a copy of TREES & HIERARCHIES IN SQL and learn how to do it one
simple query.

[Tony Rogerson]

The nested set method you will see is:-

1) Difficult to maintain
2) Does not scale nor perform well out of the lab and in the real
world.

A better method is that developed by Itzik Ben Gan, information here:
http://www.windowsitpro.com/Articles...layTab=Article

The challenge is still there, you code up using the nested set model and
i'll using Itzik's method; i have a data set of over 700,000 rows which is a
message board so will be a good test.

--
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"--CELKO--" <jc*******@earthlink.net> wrote in message
news:11*********************@f14g2000cwb.googlegro ups.com...

Get a copy of TREES & HIERARCHIES IN SQL and learn how to do it one
simple query.

[pb648174]

I have used the solution mentioned by Tony for a folder/subfolder
document management solution and it works quite well.