Mar 20 2023 08:52 AM
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?
Mar 20 2023 09:17 PM - edited Mar 20 2023 09:27 PM
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/