---生成启用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
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