SQL清空表

清空表中数据
delete from tablename
truncate table tablename
利用游标清理所有表
declare @trun_name varchar(50)
declare mycursor cursor
for select 'truncate table '+ name from sysobjects where xtype='U' and status>0
open mycursor
fetch next from mycursor into @trun_name
while @@FETCH_STATUS=0
being
exec(@trun_name)
print 'truncated table '+@trun_name
 
fetch next from mycursor into @trun_name
end
close mycursor
deallocate mycursor
 
 
1. Delete all tables
DECLARE @tablename varchar(50)
DECLARE @truncatesql varchar(255)
DECLARE TrCun_Cursor CURSOR FOR
select [name] from sysobjects where type = 'U'
-- or select [name] from sysobjects where type = 'U' and name 'table_you_do_NOT_want_to_delete'
OPEN TrCun_Cursor
FETCH TrCun_Cursor INTO
@tablename
WHILE(@@fetch_status = 0)
BEGIN
SET @truncatesql = 'truncate table ' + @tablename
--exec(@truncatesql)
PRINT @truncatesql
FETCH TrCun_Cursor INTO @tablename
END
CLOSE TrCun_Cursor
DEALLOCATE TrCun_Cursor
 
2. Truncate
EXEC sp_MSforeachtable "truncate table ?"
3. Delete and Truncate tables started with YDS
sp_MSforeachtable @command1 = "delete from ?",@whereand="and name like 'YDS_%'"
sp_MSforeachtable @command1 = "truncate table ?",@whereand="and name like 'YDS_%'"

作者“探索者”