在SQL Server中截断一些表并将其余表保存在同一个数据库中

Environment: SQL Server 2016 in Azure SQL server.

环境:Azure SQL Server中的SQL Server 2016。

I have around 600 tables in my database. Now I want to truncate 120 of them and keep rest of them. How can I do that?

我的数据库中有大约600个表。现在我要截断其中的120个并保留其余的。我怎么做呢?

Instead of using

而不是使用

Truncate table my_table_name1,
Truncate table my_table_name2,
...

Is there a faster way to pick the desired tables that I want to truncate?

是否有一种更快的方法来选择我想要截断的期望的表?

Thanks

谢谢

1 个解决方案

#1

Assuming there is some key pattern to the tables you want to truncate, perhaps a little dynamic SQL may help.

假设您想要截断的表中有一些关键的模式，也许一些动态SQL可能会有所帮助。

Declare @SQL varchar(max) = ''
Select @SQL = @SQL +';Truncate Table '+QUOTENAME(TABLE_SCHEMA)+'.'+QUOTENAME(TABLE_NAME)+char(13) From INFORMATION_SCHEMA.Tables Where Table_Name like '%OD%'

Select @SQL
--Exec(@SQL) -- Only of Satisifed with the results

Returns

返回

;Truncate Table [dbo].[OD-Date]
;Truncate Table [dbo].[OD]
;Truncate Table [dbo].[OD-OH]
;Truncate Table [dbo].[OD-Tier]
;Truncate Table [dbo].[OD-Tally]
;Truncate Table [dbo].[OD-Map]
;Truncate Table [dbo].[OD-XP]
;Truncate Table [dbo].[OD-Msg]