查询以获取 SQL Server 2000 中的所有外键约束 [英] Query to get all foreign key constraints in SQL Server 2000
问题描述
我需要查询 SQL Server 2000 以获取所有外键的列表.
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.
如何编写此查询?
推荐答案
select * from sysobjects
where xtype = 'F'
我希望这样可以解决问题并与 SQL Server 2000 兼容!
That should do the trick and be compatible with SQL Server 2000, I hope!
如果您还需要 SQL Server 2000 中的表和列信息,则涉及更多;您需要像这样加入 sysforeignkeys
和 syscolumns
目录视图:
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')
如果您想利用在 SQL Server 2000 中确实可用的 INFORMATION_SCHEMA 视图,请使用以下查询:
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
马克
这篇关于查询以获取 SQL Server 2000 中的所有外键约束的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!