get all parent tables name with child from sql 2005

[Abdul Rahman Hamidy]

Dear All, I am trying to list parent and child tables from sql 2005, but i could not find the solution. well, How can i list all parent tables first then child tables from sql server 2005 in a single statement?

Abdul Rahaman Hamidy Database Developer Kabul, Afghanistan

[Mycroft Holmes]

You may want to look into the sys objects. This view seems to have some interesting info for you sys.foreign_key_columns it is in 2008 but I think it may be there in 2005 as well.

[Abdul Rahman Hamidy]

I tried with this query but didnt help

select distinct(tb.[name]) as TableName,tb.[Object_Id] as ObjectId,fk.type_desc,fk.[type] as [type] from sys.tables tb left join sys.foreign_keys fk
on tb.[object_Id] = fk.parent_object_Id
where tb.[type]='U' and tb.is_ms_shipped=0 and tb.[name] not like 'asp%' and tb.[name] not like 'sys%'
order by fk.[type] asc

Abdul Rahaman Hamidy Database Developer Kabul, Afghanistan

[Mycroft Holmes]

This took me 10 minutes of faffing about with the system tables. Take a look at the last 2 columns

SELECT K.*,'|',O.NAME,p.name
FROM sys.foreign_keys K
INNER JOIN sysobjects O ON O.id = K.referenced_object_id
INNER JOIN sysobjects P ON P.id = K.parent_OBJECT_ID

[Abdul Rahman Hamidy]

well, i have done this which returns the requirement, but only it does not returns parent tables first then child tables. would u help me to return parent tables first than child tables.

select distinct(t.name),t.[object_id] from sys.tables t left join sys.foreign_keys k
on t.object_id=k.parent_object_id
where t.[type]='U' and t.is_ms_shipped=0 and t.[name] not like 'asp%' and t.[name] not like 'sys%'
order by t.[name] desc

Abdul Rahaman Hamidy Database Developer Kabul, Afghanistan