Querying Path through Schema

[hwkranger]

Assuming that the Schema of my database is normalized and complete.. I would like to be able to do the following:

query sysobjects or a sysview that when given the name of 2 tables would return all tables between the two tables via relationships. (I only want tables that are between the two entities... anything NOT on the critical path should not be included)

I know there has to be a way to do this, but my brain isn't working this morning.

Thanks for the help.... (If I figure this out in the interum before someone else, I'll post it -- I'm sure this wuold be helpful to the community)

Randall Vollen
National City Bank Corp.

 

[Catadmin]

What version of SQL are you using? The system tables & views have slightly different names between 2000 and 2005.



Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"

 

[hwkranger]

I'm using 2000, but -- eventually this should be ported to 2005.

Thank you.

(I didn't get a chance to work so much on this today, since I got tasked to looking at some nasty projection models.)

Randall Vollen
National City Bank Corp.

 

[Catadmin]

You'll want to play with this, but it should give you what you want. Type U means User table.

Select so.name, so.Type, so1.name as DepName, sd.*
from sysobjects so
left outer join sysdepends sd
on so.ID = sd.ID
left outer join sysobjects so1
on sd.depID = so1.ID
where so.Type = 'U'
and so1.Type = 'U'
and so.Name = 'MyTable'

Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"