外键: select name from sysobjects where parent_obj=object_id(N'表名') and xtype='F'
sp_fkeys 返回当前环境的逻键外键信息。该过程显示各种外键关系,包括禁用的外键。
EXEC sp_fkeys @pktable_name = N'表名'
--select * from sysobjects where parent_obj=object_id(N'Customer') and xtype='F'select A.name as 外键关联的表,B.name as 外键所在的表,C.name as 外键名称 from sysforeignkeys inner join sysobjects A on A.id=sysforeignkeys.fkeyid inner join sysobjects B on B.id=sysforeignkeys.rkeyid inner join sysobjects C on C.id=sysforeignkeys.constid
要写成查询语句EXEC sp_fkeys @fktable_name = N'表名'
select A.name as 外键关联的表,B.name as 外键所在的表,C.name as 外键名称 from sysforeignkeys inner join sysobjects A on A.id=sysforeignkeys.fkeyid inner join sysobjects B on B.id=sysforeignkeys.rkeyid inner join sysobjects C on C.id=sysforeignkeys.constid and C.name in (select name from sysobjects where parent_obj=object_id(N'表名') and xtype='F')
前2列写反了 select A.name as 外键关联的表,B.name as 外键所在的表,C.name as 外键名称 from sysforeignkeys inner join sysobjects A on A.id=sysforeignkeys.rkeyid --references表id inner join sysobjects B on B.id=sysforeignkeys.fkeyid --foreignkey表id inner join sysobjects C on C.id=sysforeignkeys.constid --foreighkey的id and C.name in (select name from sysobjects where parent_obj=object_id(N'表名') and xtype='F')
use pubs go select b.name,d.name from sysobjects a join sysobjects b on a.id=b.parent_obj join sysforeignkeys c on b.id=c.constid join sysobjects d on c.rkeyid=d.id where a.name='titles' and b.xtype='f'
select name from sysobjects where parent_obj=object_id(N'表名') and xtype='F'
返回当前环境的逻键外键信息。该过程显示各种外键关系,包括禁用的外键。
from sysforeignkeys
inner join sysobjects A on A.id=sysforeignkeys.fkeyid
inner join sysobjects B on B.id=sysforeignkeys.rkeyid
inner join sysobjects C on C.id=sysforeignkeys.constid
from sysforeignkeys
inner join sysobjects A on A.id=sysforeignkeys.fkeyid
inner join sysobjects B on B.id=sysforeignkeys.rkeyid
inner join sysobjects C on C.id=sysforeignkeys.constid
and C.name in (select name from sysobjects where parent_obj=object_id(N'表名') and xtype='F')
select A.name as 外键关联的表,B.name as 外键所在的表,C.name as 外键名称
from sysforeignkeys
inner join sysobjects A on A.id=sysforeignkeys.rkeyid --references表id
inner join sysobjects B on B.id=sysforeignkeys.fkeyid --foreignkey表id
inner join sysobjects C on C.id=sysforeignkeys.constid --foreighkey的id
and C.name in (select name from sysobjects where parent_obj=object_id(N'表名') and xtype='F')
go
select b.name,d.name from sysobjects a join sysobjects b on a.id=b.parent_obj
join sysforeignkeys c on b.id=c.constid
join sysobjects d on c.rkeyid=d.id
where a.name='titles' and b.xtype='f'