查询以获取SQL Server 2000中的所有外键约束 [英] Query to get all foreign key constraints in SQL Server 2000

问题描述

我需要SQL Server 2000的查询来获取所有外键的列表。

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

>

如何写这个查询？

解决方案

  select * from sysobjects 
 where xtype ='F'
  

技巧和与SQL Server 2000兼容，我希望！

如果您另外需要SQL Server 2000中的表和列信息，它会涉及更多一点;您需要加入 sysforeignkeys 和 syscolumns 目录视图，如下所示：

 选择
 so.name'外键名称'，
 OBJECT_NAME（parent_obj）'表'，
 OBJECT_NAME（sf.fkeyid）'引用表，
 sc1.name'referencing column'，
 OBJECT_NAME（sf.rkeyid）'引用表'，
 sc2.name'引用列'
 from sysobjects so 
内部连接sysforeignkeys sf在so.id = sf.constid 
内部连接syscolumns sc1在sf.fkeyid = sc1.id和sf.fkey = sc1.colid 
内部连接syscolumns sc2在sf上。 rkeyid = sc2.id和sf.fkey = sc2.colid 
其中so.xtype（'F'，'PK'）
  

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

 选择
 rc.CONSTRAINT_NAME，
 rcu.TABLE_NAME'引用表'，
 rcu.COLUMN_NAME'引用Colu mn'，
 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

b $ b

I need a query for SQL Server 2000 to get a list of all foreign keys.

Particularly all the foreign keys that point to a particular column.

How do I write this query?

解决方案

select * from sysobjects
where xtype = 'F'

That should do the trick and be compatible with SQL Server 2000, I hope!

If you additionally need the table and column information in SQL Server 2000, it gets a bit more involved; you need to join the sysforeignkeys and syscolumns catalog views like so:

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')

And if you want to leverage the INFORMATION_SCHEMA views which ARE indeed available in SQL Server 2000, use this query:

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

这篇关于查询以获取SQL Server 2000中的所有外键约束的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！