declare @trun_name varchar(8000) set@trun_name='' select@trun_name=@trun_name +'truncate table '+[name]+' ' from sysobjects where xtype='U'and status > 0 exec (@trun_name)
该方法适合表不是非常多的情况,否则表数量过多,超过字符串的长度,不能进行完全清理.
2.利用游标清理所有表
declare @trun_name varchar(50) declare name_cursor cursor for select'truncate table '+ name from sysobjects where xtype='U'and status > 0 open name_cursor fetch next from name_cursor into@trun_name while @@FETCH_STATUS = 0 begin exec (@trun_name) print'truncated table '+@trun_name fetch next from name_cursor into@trun_name end close name_cursor deallocate name_cursor