SQL Server 2000 - 查询表的外键关系

Question

这与查询表的外键关系几乎相同，但适用于SQL Server 2000

对于给定的表'foo'，我需要一个查询来生成一组具有指向foo的外键的表。

- Macho Matt

Answer 1

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

---

如果您也想要列名（感谢Alex）：选择 o2.name， col_name(rkeyid, rkey) as referenced_column_name
从sysobjects o 内部连接sysforeignkeys fk on o.id = fk.rkeyid 内部连接sysobjects o2 on fk.fkeyid = o2.id 其中o.name ='ATE_Question' - Macho Matt

Answer 2

尝试使用以下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

- alex

---

请恕我直言，到底是谁在这里给我投了反对票？请站出来。 - alex

Answer 3

家长和孩子

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

---

这正是所要求的，甚至更多。 - StingyJack

我运行了它，但没有任何输出。在将其标记为下降之前应该对此进行评论。 - TamusJRoyce

如果你运行它并且没有输出，那就意味着没有定义外键。 - Davin Studer

Answer 4

从这里开始

    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

[编辑] 格式全乱了

[编辑2] 不再

- SQLMenace

---

给定一个名为“foo”的表，我需要一条查询来生成一组具有指向foo的外键的表。 关闭...授信一个有效的查询。 - Macho Matt