how to run same query on multiple databases in sql server

Question

I have Single Instant and around 15-20 Database i need to run this below query for each database
currently i use this query

use [A]
Delete from DeviceCommands
use [B]
Delete from DeviceCommands
use [C]
Delete from DeviceCommands
use [D]
Delete from DeviceCommands
.
.
.
.
.
.
use [O]
Delete from DeviceCommands

This process is too long time

But i want to run "Delete from DeviceCommands" this command run single query on multiple databases

Answer 1

The easiest way to achieve this is what Viorel-1 stated above -

Viorel-1 · 3 hours ago
There is an internal sp_MSforeachdb, and maybe you can use it:
https://learn.microsoft.com/en-us/answers/search.html?q=sp_MSforeachdb
https://www.bing.com/search?q=sp_MSforeachdb

Or use PowerShell with the module dbatools and execute it from outside SQL Server...

Get-DbaDatabase -SqlInstance "server1" | Invoke-DbaQuery -File "C:\scripts\sql\rebuild.sql"  

Invoke-DbaQuery

Answer 2

With one DELETE statement you can address one table, not more, so "one single query" is not possible.

Answer 3

Can you try Truncate command for your query?

use [A];

Truncate table DeviceCommands;
--or
Truncate table [A].dbo.DeviceCommands;

If it runs faster, you can apply to all databases with an option you choose from other replies.

Answer 4

   DECLARE @sql nvarchar(MAX)  
     
   SELECT @sql = string_agg('DELETE ' + quotename(DB) + '.dbo.DeviceCommands', char(13) + char(10))  
   FROM  (VALUES('Db1'),('Db2'),('Db3')) AS D(DB)  
     
   PRINT @sql  
     
   EXEC (@sql)  

You could also put the DB names in a fixed table. If it's all tables on the server but the system databases, you could use sys.databses WHERE database_id > 5.

Answer 5

Hi @Devendra Kumar Sahu ,

Agree with them. You can use sp_MSforeachdb.
Please refer to this blog: Run same command on all SQL Server databases without cursors
Hope this will give you some help.

Best regards,
Seeya

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.