标签归档:外键

mssql SQL Server 2008 查询所有表外键信息的方法分享


摘要:
下文讲述sqlserver 2008获取表外键的方法
实验环境:sql server 2008 R2



实现思路:
主要根据系统表 syscolumns, sysobjects, sysforeignkeys
获取外键列的相关信息,如下:

 
   SELECT 
(SELECT name FROM syscolumns WHERE colid=b.rkey AND id=b.rkeyid) 
 as [主键-列名],
object_name(b.rkeyid) as [主键-表名],
object_name(b.fkeyid)  as [外键-表名],
(SELECT name FROM syscolumns 
			WHERE colid=b.fkey AND id=b.fkeyid)  as [外键列名]
FROM sysobjects a join sysforeignkeys b on a.id=b.constid 
	join sysobjects c on a.parent_obj=c.id 
where a.xtype='f' AND c.xtype='U'   

mssql sqlserver 禁用和启用外键约束的方法分享


摘要:
下文通过举例的方式分享,禁用和启用指定外键的方法,如下所示:
实验环境:sqlserver 2008 R2


     
---禁用和启用指定表名的所有 约束(包含外键约束)
alter table [tableName]  NOCHECK constraint all; ---禁用所有约束 
alter table [tableName]  CHECK constraint all;   ---启用所有约束
   
---使用sql脚本生成指定外键约束(禁用和启用脚本)
select 'ALTER TABLE ' + b.name + ' NOCHECK CONSTRAINT ' + a.name +';'  from sysobjects a ,sysobjects b where a.xtype ='f' 
and a.parent_obj = b.id and b.name='tableName'; 
---生成禁用脚本


select 'ALTER TABLE ' + b.name + ' CHECK CONSTRAINT ' + a.name +';'  from sysobjects a ,sysobjects b where a.xtype ='f' 
and a.parent_obj = b.id and b.name='tableName'; 
---生成启用脚本

--查看当前数据库下的所有外键及外键状态
select name  as [外键名称],
 case  is_disabled 
 when 1 then '启用'
 else '禁用'
 end as [外键状态]
   from sys.foreign_keys