项目开发与测试过程填充大量测试数据，实施前需要清空数据库，整理一个干净的数据环境。手写delete语句很费事，所以从网上搜索相关内容整理如下

declare @trun_name varchar(500) declare name_cursor cursor for--禁用外键--select  'ALTER TABLE ['  + name +  '] NOCHECK CONSTRAINT  all '  from  sysobjects  a  where  a.xtype ='u'  --禁用触发器--select  'ALTER TABLE ['  + name +  '] DISABLE  TRIGGER  all '  from  sysobjects  a  where  a.xtype ='u'  --清空表--select 'truncate table ' + name from sysobjects where xtype='U' and name not in ('xt_mksz','xt_mkczgn','xt_dwbm') order by name--排除不需要清空的表，以及清空过程报错的表需要单独处理--开启外键--select  'ALTER TABLE ['  + name +  '] CHECK CONSTRAINT  all '  from  sysobjects  a  where  a.xtype ='u'  --开启触发器--select  'ALTER TABLE ['  + name +  '] enable  TRIGGER  all '  from  sysobjects  a  where  a.xtype ='u'  --所有identity表复原为1--select  'dbcc checkident(['+name+'],reseed ,1) ' from  sysobjects  a  where  a.xtype ='u'  and objectproperty(id,'TableHasIdentity')=1  and name not in ('xt_mksz','xt_mkczgn','xt_dwbm')--排除不需要清空的表，以及清空过程报错的表需要单独处理   --重建索引--select  'dbcc DBREINDEX(['+name+']) '  from  sysobjects  a  where  a.xtype ='u'      open name_cursor fetch next from name_cursor into @trun_name while @@FETCH_STATUS = 0 begin   exec (@trun_name)  print @trun_name  fetch next from name_cursor into @trun_name end close name_cursor deallocate name_cursor

以上语句，用于sql sever 上数据库清空 ，需要把注释的语句依次执行。

本文内容来源于网络搜集，如有版权问题请联系告知