SQL语句清空所有table中的记录

-----此处不用truncate是因为truncate必须得删除外键,如果不考虑外键的话最好用truncate

  ----禁用当前数据库中所有表的约束、触发器
  exec sp_msforeachtable 'ALTER TABLE ? DISABLE TRIGGER all'
  exec sp_msforeachtable 'ALTER TABLE ? NOCHECK CONSTRAINT all'

go

----清空除employee、department、account外的所有表中的记录
begin
  declare crsr cursor            ------定义游标取回用户建立的表
    for SELECT [name] FROM DBO.SYSOBJECTS
       WHERE OBJECTPROPERTY(ID,N'IsTable')=1 and type = 'U' and ([name] <> 'dtproperties'
     and [name] <> 'account' and [name] <> 'employee' and [name]<>'department' )
 
  declare @tblName sysname       ------用户表名

  open crsr
    fetch crsr into @tblName
    EXEC('  delete from  dbo.'+@tblName)
    print @tblName
    while @@fetch_status=0 
     begin   
      fetch next from crsr into @tblName
      exec('delete from dbo.'+@tblName)
      print @tblName
     end 
  close crsr
deallocate crsr
end

go

  ----启用当前数据库中所有表的约束、触发器
  exec sp_msforeachtable 'ALTER TABLE ? enable TRIGGER all'
  exec sp_msforeachtable 'ALTER TABLE ? CHECK CONSTRAINT all'