Posted by Erland Sommarskog on 09/30/45 11:31

(pankaj_wolfhunter@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, esquel@sommarskog.se

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

[Back to original message]