在SQL Server 2000中获取所有外键约束的查询

Question

我需要一个查询SQL Server 2000的语句，以获取所有外键的列表。

特别是指向特定列的所有外键。

我该如何编写这个查询语句？

- Kevin

Answer 1

select * from sysobjects
where xtype = 'F'

希望这样做可以解决问题，并且与SQL Server 2000兼容！

如果您还需要在SQL Server 2000中获取表格和列信息，则需要进行更多的操作；您需要像这样连接sysforeignkeys和syscolumns目录视图：

select
  so.name 'foreign key name',
  OBJECT_NAME(parent_obj) 'table',
  OBJECT_NAME(sf.fkeyid) 'referencing table',
  sc1.name 'referencing column',
  OBJECT_NAME(sf.rkeyid) 'referenced table',
  sc2.name 'referenced column'
from sysobjects so
inner join sysforeignkeys sf on so.id = sf.constid
inner join syscolumns sc1 on sf.fkeyid = sc1.id and sf.fkey = sc1.colid
inner join syscolumns sc2 on sf.rkeyid = sc2.id and sf.fkey = sc2.colid
where so.xtype in ('F','PK')

如果您想利用在SQL Server 2000中确实可用的INFORMATION_SCHEMA视图，请使用以下查询：

SELECT
    rc.CONSTRAINT_NAME,     
    rcu.TABLE_NAME 'Referencing Table', 
    rcu.COLUMN_NAME 'Referencing Column',
    rcu1.TABLE_NAME 'Referenced Table',
    rcu1.COLUMN_NAME 'Referenced Column'
FROM
    INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS rc
INNER JOIN 
    INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE rcu 
      ON rc.CONSTRAINT_CATALOG = rcu.CONSTRAINT_CATALOG 
         AND rc.CONSTRAINT_NAME = rcu.CONSTRAINT_NAME
INNER JOIN 
    INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE rcu1 
      ON rc.UNIQUE_CONSTRAINT_CATALOG = rcu1.CONSTRAINT_CATALOG 
         AND rc.UNIQUE_CONSTRAINT_NAME = rcu1.CONSTRAINT_NAME

马克

- marc_s

---

第二个查询似乎有点奇怪。它似乎缺少某种连接条件 - 我得到了一些笛卡尔积。 - Rashack

实际上，您需要加入 sf.fkeyid = sc1.id and sf.fkey = sc1.colid（rkey也是同样） - Mark

你可能还想使用 so.xtype in ('F', 'PK')。 - Mark

这正是我正在寻找的确切解决方案。谢谢。 - Gary Kindel

应该使用 and sf.rkey = sc2.colid 联接参考列，而不是 and sf.fkey = sc2.colid。 - Pedro Custódio

Answer 2

select * from INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS

如果您需要更多关于键的信息，可以将其连接到 INFORMATION_SCHEMA.KEY_COLUMN_USAGE 视图中，该视图包含被键引用的列。

- David McEwing

---

我认为在 SQL Server 2000 中没有 INFORMATION_SCHEMA...(我想那是在2005年才新出的，对吗？) - marc_s

有的。在发布之前，我已经对它进行了SQL 2000实例的检查。 - David McEwing

你如何在列上进行筛选？ - KM.

Answer 3

查看 sp_helpconstraint 的源代码以获取更多想法，但这应该可以工作...

获取指向目标表和列的每个 FK

• 替换 "YourTableName"
• 取消注释最后一个 "AND" 并设置您的目标列名称

代码：

--list all tables & columns that refer to the given table
select
    k.name,pt.Name AS ParentTable,pc.name,c.constraint_column_id,ct.Name AS ReferedToTable,c.referenced_column_id,cc.Name
    from sys.foreign_keys                  k
        INNER JOIN sys.foreign_key_columns c ON k.parent_object_id=c.parent_object_id
        INNER JOIN sys.objects             pt ON c.parent_object_id=pt.object_ID
        INNER JOIN sys.objects             ct ON c.referenced_object_id=ct.object_ID
        INNER JOIN  sys.columns            pc ON c.parent_object_id=pc.object_ID AND c.parent_column_id=pc.column_id
        INNER JOIN  sys.columns            cc ON c.referenced_object_id=cc.object_ID AND c.referenced_column_id=cc.column_id
    where k.referenced_object_id = object_id('YourTableName')
        --AND pc.name='YourColumnName' --parent table column name
        --AND cc.name='YourColumnName' --referenced table's column name

- KM.

---

再次强调 - "sys"模式在SQL Server 2000中不可用...但在2005和2008中可以正常使用。 - marc_s