CODEWITHSUNDEEP
sql-serverazuretruncate
Jailene Huang
Jailene Huang

Reputation: 173

Truncate some tables and keep rest of tables in the same database in SQL Server

Environment: SQL Server 2016 in Azure SQL server.

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?

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

Upvotes: 2

Views: 52

Answers (1)

John Cappelletti
John Cappelletti

Reputation: 81960

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

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]

Upvotes: 3

Related Questions