设为首页 加入收藏

TOP

SQL清空表
2014-11-24 01:44:53 】 浏览:4861
Tags: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_%'"


作者“探索者”

】【打印繁体】【投稿】【收藏】 【推荐】【举报】【评论】 【关闭】 【返回顶部
上一篇DB2 9存储过程的规划和实施技巧 下一篇SQLSERVER数据类型转换

最新文章

热门文章

Hot 文章

Python

C 语言

C++基础

大数据基础

linux编程基础

C/C++面试题目