SqlServer禁用、启用外键约束

发布时间：2020-12-12 13:53:45 所属栏目：MsSql教程来源：网络整理

导读：---生成启用or禁用指定表外键约束的sql select 'ALTER TABLE ' + b.name + ' NOCHECK CONSTRAINT ' +a.name +';' ? from sysobjects a,sysobjects b wherea.xtype ='f' and a.parent_obj = b.id ; select 'ALTER TABLE ' + b.name + ' CHECK CONSTRAINT ' +

---生成启用or禁用指定表外键约束的sql select 'ALTER TABLE ' + b.name + ' NOCHECK CONSTRAINT ' +a.name +';' ? from sysobjects a,sysobjects b wherea.xtype ='f' and a.parent_obj = b.id ; select 'ALTER TABLE ' + b.name + ' CHECK CONSTRAINT ' + a.name+';' ? from sysobjects a,sysobjects b wherea.xtype ='f' and a.parent_obj = b.id ; DECLARE @i INT DECLARE @nochecksql NVARCHAR(MAX) SET @nochecksql='' SELECT @i=COUNT(*) from sysobjects where xtype='u' WHILE(@i>0) BEGIN ?? ?SELECT @nochecksql ='exec sp_msforeachtable ''alter table ['+name+'] nocheck constraint all'';'+CHAR(10) FROM( ?? ?SELECT ROW_NUMBER() OVER(ORDER BY name) AS rownum,name? from sysobjects where xtype='u') AS T ?? ?WHERE rownum=@i ?? ? ?? ?SET @i=@i-1 ?? ?PRINT (@nochecksql); END PRINT @nochecksql SELECT @i=COUNT(*) from sysobjects where xtype='u' DECLARE @checksql NVARCHAR(MAX) WHILE(@i>0) BEGIN ?? ?SELECT @nochecksql+='exec sp_msforeachtable ''alter table ['+name+'] CHECK constraint all'';'+CHAR(10) FROM( ?? ?SELECT ROW_NUMBER() OVER(ORDER BY name) AS rownum,name? from sysobjects where xtype='u') AS T ?? ?WHERE rownum=@i ?? ? ?? ?SET @i=@i-1 END PRINT @nochecksql

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!