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