SQL Server 关闭及开启所有约束

declare @tbname varchar(250)   
declare #tb cursor for select name from sysobjects where objectproperty(id,'IsUserTable')=1   
open #tb   
fetch next from #tb into @tbname   
while @@fetch_status=0   
begin  
  exec('alter table ['+@tbname+'] nocheck constraint all')    
  -- nocheck关闭所有约束 check开启所有约束   
  fetch next from #tb into @tbname   
end  
close #tb   
deallocate #tb
 

 

 

EXEC sp_MSForEachTable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL'
EXEC sp_MSForEachTable 'ALTER TABLE ? DISABLE TRIGGER ALL'
EXEC sp_MSForEachTable 'DELETE FROM ?'
EXEC sp_MSForEachTable 'ALTER TABLE ? CHECK CONSTRAINT ALL'
EXEC sp_MSForEachTable 'ALTER TABLE ? ENABLE TRIGGER ALL'
EXEC sp_MSFOREACHTABLE 'SELECT * FROM ?'