SqlServer禁用、启用外键约束

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