SQL 2000：T-SQL获取表的外键关系 [英] SQL 2000: T-SQL to get foreign key relationships for a table

问题描述

SQL Server 2000 - 查询一个类似但不是相同的表的外键关系

我需要一个T-SQL语句，它可以在给定表名的情况下工作SQL 2000，并返回该表的外键关系例如

Table MyFristTable具有MySecondTable的外键，其中MyFirstTable.ColA必须位于MySecondTable.ColB中。我会很高兴，如果sql语句（或存储过程）运行MyFirstTable并返回结果集的行

 列| FK_Table | FK_COLUMN 
 ---------------------------------- 
 ColA | MySecondTable | ColB 
  

注意：因为它们依赖于sys.foreign_key_columns

我不想解析出sp_help语句的结果。

谢谢，

解决方案

  DECLARE @tableName sysname 
 
 SET @tableName ='' - 你的表名在这里
 
 SELECT 
 c.name 
，target.name 
，targetc.name 
 FROM 
  - 源表
 sysobjects t 
  - 源列
 INNER JOIN syscolumns c ON t.id = c.id 
  - 一般约束
 INNER JOIN sysconstraints co ON t.id = co.id AND co.colid = c.colid 
  - 外键约束
 INNER JOIN sysforeignkeys fk ON co.constid = fk.constid 
 - 目标表
 INNER JOIN sysobjects target ON fk.rkeyid = target.id 
  - 目标列
 INNER JOIN syscolumns targetc ON fk.r key = targetc.colid AND fk.rkeyid = targetc.id 
 WHERE 
 t.name = @tableName 
  

注意我只使用SQL 2000中可用的系统视图（即sysXXX系统视图，而不是SQL 2005 sys.XXX系统视图），但实际上我只测试过在SQL 2005环境中。

Similar but NOT IDENTICAL to SQL Server 2000 - Query a Table’s Foreign Key relationships

I need a T-SQL statement that will work SQL 2000 that given a table name, will return the foreign key relationships for that table e.g.

Table MyFristTable has a foreign key to MySecondTable, where MyFirstTable.ColA must be in MySecondTable.ColB. I'd be delighted, if the sql statement (or stored proc) is ran for MyFirstTable and returned a result set on the lines of

Column | FK_Table      | FK_COLUMN
----------------------------------
ColA   | MySecondTable | ColB

NB: I have samples for SQL 2005 that won't work because they rely on sys.foreign_key_columns

I'd rather not have to parse out the results of the sp_help statement.

Thanks,

解决方案

DECLARE @tableName sysname

SET @tableName = '' -- Your table name goes here

SELECT
    c.name
    , target.name
    , targetc.name
FROM
    -- source table
    sysobjects t
    -- source column
    INNER JOIN syscolumns c ON t.id = c.id
    -- general constraint
    INNER JOIN sysconstraints co ON t.id = co.id AND co.colid = c.colid
    -- foreign key constraint
    INNER JOIN sysforeignkeys fk ON co.constid = fk.constid
    -- target table
    INNER JOIN sysobjects target ON fk.rkeyid = target.id
    -- target column
    INNER JOIN syscolumns targetc ON fk.rkey = targetc.colid AND fk.rkeyid = targetc.id
WHERE
    t.name = @tableName

NOTE I have I think used only those system views available in SQL 2000 (ie the sysXXX ones rather than the SQL 2005 sys.XXX ones) but I have only actually tested this in a SQL 2005 environemnt.

这篇关于SQL 2000：T-SQL获取表的外键关系的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！