Obtain fields that form relationships thru T-SQL

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

Transact-SQL (2000)

Obtain fields that form relationships thru T-SQL

Author

Topic

itfanaticus
Starting Member

6 Posts

Posted - 2006-03-23 : 10:50:11

Has anyone ever attempted to obtain the fields used in a relationship thru T-SQL? Of course, one would have to use the system tables.

For instance:
Table1(table1ID, ...)
Table2_LK(table1ID,table3ID)
Table3(table3ID, ...)

So, would it be possible to get returned through T-SQL?

Table | field
--------------
Table1 | table1ID
Table3 | table3ID

I'm generating T-SQL through a server side engine and then running it. It would be cool to get this in order to clean linking tables that may not have had the proper relationships to constrain the data entered into them.

So far I have this:

select * from sysObjects where id in (
select rkeyID
from sysForeignKeys fk where fkeyid in (
SELECT o.id from sysObjects o
where o.name = 'Table2_LK'))

This would return to me, the two tables which have relationships with the specified table, in this case, "Table2_LK".

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2006-03-23 : 11:00:04

I'd suggest using the metadata system SPs rather than directly pulling from system tables. The SPs will remain supported over sql server version changes where as the system table may change. ie:

use pubs
exec sp_fkeys 'authors'
exec sp_pkeys 'authors'
exec sp_helpconstraint 'authors'

Be One with the Optimizer
TG

itfanaticus
Starting Member

6 Posts

Posted - 2006-03-23 : 11:06:14

Ha! Just like that! Nice.

I have been wrestling with this for a while now ;)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources