views:

1472

answers:

4

Nearly identical to http://stackoverflow.com/questions/85978/query-a-tables-foreign-key-relationships, but for SQL Server 2000

For a given table 'foo', I need a query to generate a set of tables that have foreign keys that point to foo.

A: 

Start here

SELECT cons.TABLE_NAME
, cons.CONSTRAINT_NAME PK_NAME
, cols.COLUMN_NAME

FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS cons LEFT join INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE cols ON cons.CONSTRAINT_NAME = cols.CONSTRAINT_NAME WHERE cons.CONSTRAINT_TYPE = 'foreign KEY' ORDER BY cons.TABLE_NAME , cons.CONSTRAINT_NAME , cols.COLUMN_NAME

[edit] formatting is all messed up

SQLMenace
close... Credit for a query that works. For a given table 'foo', I need a query to generate a set of tables that have foreign keys that point to foo.
Macho Matt
+2  A: 

Try this T-SQL:

select col_name(fkeyid, fkey) as column_name, object_name(rkeyid) as referenced_table_name, col_name(rkeyid, rkey) as referenced_column_name from sysforeignkeys where object_name(fkeyid) = 'tableNameHere' order by constid

I've rewritten the query slightly to give you all of the other tables that rely on a particular table:

select object_name(fkeyid),
col_name(fkeyid, fkey) as column_name,
col_name(rkeyid, rkey) as referenced_column_name
from sysforeignkeys
where object_name(rkeyid) = 'tableNameHere'
order by constid
alex
with all due respect, who the hell voted me down here? Out yourself.
alex
Seriously. WTF is that?
StingyJack
A: 

Parents and children

/* this will find out all of the foreign key references for a table*/

DECLARE @tableName varchar(128)
SET @tableName = 'tCounter'

SELECT   
    pt.[name] AS 'parentTable',  
    ct.[name] AS 'childTable',  
    fk.[name] AS 'fkName', 
*   
FROM sys.foreign_keys fk        
    INNER JOIN sys.tables pt              
     ON pt.object_ID = fk.parent_object_id        
    INNER JOIN sys.tables ct              
     ON ct.object_ID = fk.referenced_object_id   
WHERE pt.name = @tableName      
    OR ct.name = @tableName   
ORDER BY pt.name, ct.name
StingyJack
This is exactly what was asked for and then some.
StingyJack
+3  A: 
SELECT o2.name
FROM sysobjects o
INNER JOIN sysforeignkeys fk on o.id = fk.rkeyid
INNER JOIN sysobjects o2 on fk.fkeyid = o2.id
WHERE o.name = 'foo'
devio
If you want the column names too (thanks alex): SELECT o2.name, col_name(rkeyid, rkey) as referenced_column_name FROM sysobjects o INNER JOIN sysforeignkeys fk on o.id = fk.rkeyid INNER JOIN sysobjects o2 on fk.fkeyid = o2.id WHERE o.name = 'ATE_Question'
Macho Matt