SQL Server 2000 - Запрос отношения Внешнего ключа Таблицы

Родители и дети

/* 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

Попробуйте этот 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

Я переписал запрос немного, чтобы дать Вам все другие таблицы, которые полагаются на конкретную таблицу:

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

Запустите здесь

    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

[редактирование] форматирование все испорчено

[edit2] больше