Querying Parent table - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Querying Parent table

Author

Topic

Sean_B
Posting Yak Master

111 Posts

Posted - 2006-12-20 : 09:45:02

Hi,

I've written the following query to give me a list of tables and their parent table where one exists.

SELECT tab.table_name, right(con.constraint_name, charindex('_', reverse(con.constraint_name))-1)
FROM INFORMATION_SCHEMA.tables tab
left outer join INFORMATION_SCHEMA.TABLE_CONSTRAINTS con on con.table_name = tab.table_name
and constraint_type = 'FOREIGN KEY'
where table_type like '%TABLE%'
order by tab.table_name

This seems to work but I was wondering if there isn't an easier way of doing this, have I overlooked another INFORMATION_SCHEMA view, that gives these relationships ?

Sean

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-12-20 : 10:21:51

See this topic http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=72957

Peter Larsson
Helsingborg, Sweden

Sean_B
Posting Yak Master

111 Posts

Posted - 2006-12-20 : 10:28:12

Brilliant thanks,

I just noticed that my query doesn't work anyway.

Sean

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources