Forum Discussion

HishamAllop's avatar
HishamAllop
Copper Contributor
Mar 20, 2023

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

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

Sort By
  • Ronen_Ariely's avatar
    Ronen_Ariely
    MVP
    Mar 21, 2023

    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/ 

Resources