T-SQL - Delete&Truncate tables

最新推荐文章于 2024-03-19 08:30:00 发布

xxdxxd

最新推荐文章于 2024-03-19 08:30:00 发布

阅读量119

点赞数

文章标签： SQL

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_%'"