Is there SQL feature to execute script from one server to be applied in many database?

Copper Contributor

I have to execute one script in 20 database, all databases has same tables with same structures.

 

I am asking if there is any feature in SQL to help me in order to execute the script from on database to be applied for all. If there, can i monitor which server has been done and which is not?

1 Reply

Hi @HishamAllop 

 

Option 1: You can use a Cursor for all databases and run the query in a loop

 

Option 2: You can use sp_MSforeachdb

 

SQL Server has a build-in stored procedure for this task. It's name is sp_MSforeachdb. Using this SP you can run a code which will be executed on all databases. You can use a question mark as a Placeholder for the name of the database.

For example, the following query will return the name of the database for each database:

 

DECLARE @command varchar(1000) 
SELECT @command = 'USE ? SELECT [Database_Name] = "?"'
EXEC sp_MSforeachdb @command 

 

The following query will get the size of the database for each database

 

DECLARE @command VARCHAR(255)
SET @command='use [?]; exec sp_spaceused'
EXEC sp_MSforeachdb @command

 

If you want to execute the query on all databases except the system databases then you can use IF like below:

exec sys.sp_MSForeachdb
'
-- exit if system database
if ''?'' in (''master'',''model'',''msdb'',''tempdb'') return
use ?
exec sp_spaceused
'

But!!! Before you start, check this post for more understanding about the limitations

https://eitanblumin.com/2021/08/05/simplest-alternative-to-sp_msforeachdb/