SQL 在一个 MySQL 语句中截断多个表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15926826/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Truncate multiple tables in one MySQL statement
提问by user2206834
Is there a possibility to truncate with one SQL statement, multiple tables?
是否有可能使用一个 SQL 语句、多个表进行截断？
Like this:
像这样：
truncate table #OBJ_AvailabilityTraining, #OBJ_AvailabilityHoliday, #Dates_temp;
Regards
问候
采纳答案by IndoKnight
No, you can only truncate a single table with TRUNCATE command. To truncate multiple tables you can use T-SQL and iterate through table names to truncate each at a time.
不，您只能使用 TRUNCATE 命令截断单个表。要截断多个表，您可以使用 T-SQL 并遍历表名以一次截断每个表。
DECLARE @delimiter CHAR(1),
@tableList VARCHAR(MAX),
@tableName VARCHAR(20),
@currLen INT
SET @delimiter = ','
SET @tableList = 'table1,table2,table3'
WHILE LEN(@tableList) > 0
BEGIN
SELECT @currLen =
(
CASE charindex( @delimiter, @tableList )
WHEN 0 THEN len( @tableList )
ELSE ( charindex( @delimiter, @tableList ) -1 )
END
)
SELECT @tableName = SUBSTRING (@tableList,1,@currLen )
TRUNCATE TABLE @tableName
SELECT tableList =
(
CASE ( len( @tableList ) - @currLen )
WHEN 0 THEN ''
ELSE right( @tableList, len( @tableList ) - @currLen - 1 )
END
)
END
You can have all your table names comma separated in @tableList variable and yes you can truncate multiple tables from different schemas if they are prefixed.
您可以在 @tableList 变量中将所有表名以逗号分隔，是的，如果它们带有前缀，您可以截断来自不同模式的多个表。
回答by Pankaj Agarwal
You can use the sp_MSforeachtable stored procedure like so:
您可以像这样使用 sp_MSforeachtable 存储过程：
USE MyDatabase
EXEC sp_MSforeachtable 'TRUNCATE TABLE ?'
Or you can create SQL Statement
或者您可以创建 SQL 语句
SELECT concat('TRUNCATE TABLE ', TABLE_NAME, ';')
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME LIKE 'TableName%'
and run this above SQL statement
并运行上面的 SQL 语句
回答by Luv
No. But there's an alternative:
不，但还有一个选择：
SELECT 'TRUNCATE TABLE '+TABLE_NAME+ ';'
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME LIKE 'your-table-name%'
Example :
例子 ：
SELECT 'TRUNCATE TABLE '+TABLE_NAME+ ';'
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME LIKE 'TBL_ORDERS_20%'
Now you have below Results for above SelectQuery
现在您有上述Select查询的以下结果
TRUNCATE TABLE TBL_ORDERS_2001
TRUNCATE TABLE TBL_ORDERS_2002
TRUNCATE TABLE TBL_ORDERS_2003
TRUNCATE TABLE TBL_ORDERS_2004
or you can use something like
或者你可以使用类似的东西
select 'Truncate table ' + TABLE_NAME from INFORMATION_SCHEMA.TABLES where TABLE_NAME in ('Table1', 'Table2')
Update:
更新：
Looking at table in your Example Queryin your Question
Query在您的问题中查看示例中的表格
truncate table #OBJ_AvailabilityTraining, #OBJ_AvailabilityHoliday, #Dates_temp
truncate table #OBJ_AvailabilityTraining, #OBJ_AvailabilityHoliday, #Dates_temp
I think you want to Truncateall temporary tables.
我想你想要Truncate所有的临时表。
You can do so with a simple Querylike this
你可以用这样的简单来做到Query这一点
select 'Truncate table ' + TABLE_NAME from tempdb.INFORMATION_SCHEMA.TABLES
回答by doublejosh
Bonus example, truncate all tables in one database starting with a string...
奖励示例，截断一个数据库中以字符串开头的所有表...
SELECT CONCAT('TRUNCATE TABLE ', TABLE_NAME, ';')
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = 'my_database_name'
AND TABLE_NAME LIKE 'cache_%';
回答by Alfa Renaldo Aluska
Just easily enter this MySQL statement:
只需轻松输入此 MySQL 语句：
TRUNCATE TABLE yourtablename1;
TRUNCATE TABLE yourtablename2;
TRUNCATE TABLE yourtablename3;
... and so on.
... 等等。
Change the yourtablenamewith yours.
用你的名字改变你的表名。
Don't forget the semicolon ;
不要忘记分号；
回答by Xavi Duro
I have had to change the code sent by IndoKnight because it throws me an error in the truncate statement so I change the code by this, it is quite similar but the truncate part:
我不得不更改 IndoKnight 发送的代码，因为它在 truncate 语句中抛出了一个错误，所以我通过这个更改了代码，它非常相似，但截断部分：
DECLARE @delimiter CHAR(1),
@tableList VARCHAR(MAX),
@tableName VARCHAR(100),
@currLen INT,
@truncateStatement VARCHAR(200)
SET @delimiter = ','
SELECT @tableList = COALESCE(@tableList + ', ','') + CAST (TABLE_NAME AS varchar(100))
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = 'xxx'
WHILE LEN(@tableList) > 0
BEGIN
SELECT @currLen =
(
CASE charindex( @delimiter, @tableList )
WHEN 0 THEN len( @tableList )
ELSE ( charindex( @delimiter, @tableList ) -1 )
END
)
SELECT @tableName = TRIM(SUBSTRING (@tableList,1,@currLen ))
SET @truncateStatement = 'TRUNCATE TABLE ' + QUOTENAME('xxx') + '.' + QUOTENAME(@tableName)
EXEC (@truncateStatement)
SELECT @tableList =
(
CASE ( len( @tableList ) - @currLen )
WHEN 0 THEN ''
ELSE right( @tableList, len( @tableList ) - @currLen - 1 )
END
)
END
The trick is using QUOTENAME to put the square brackets to the table name and schema. Maybe IndoKnight solution does not work anymore in more recent version of the database. I hope it helps.
诀窍是使用 QUOTENAME 将方括号放在表名和模式中。也许 IndoKnight 解决方案在更新版本的数据库中不再起作用。我希望它有帮助。
