How can I run the same command on all SQL Server databases without cursors
The stored procedure sp_MSforeachdb allows us to iterate through each database in a SQL Server instance without a loop statement.
It’s usage is similar than sp_MSforeachtable; simply execute the stored procedure, passing it the command that you’d like to execute as a string.
For this example, we will truncate the transaction log file on all databases (but exclude system DBs as master, msdb, model and tempdb).
Below is a step-by-step instruction on how to do this:
- Call stored procedure sp_MSForeachdb
- Display current db for information
- Set variable to transaction log name
- Change the database recovery model to SIMPLE for the database
- Shrink the truncated log file to 1 MB
EXEC master..sp_MSForeachdb ' USE [?] IF ''?'' <> ''master'' AND ''?'' <> ''model'' AND ''?'' <> ''msdb'' AND ''?'' <> ''tempdb'' BEGIN -- display current db SELECT ''?''; -- set transaction log name DECLARE @FileName varchar(300) SELECT @FileName = name from sys.database_files where type=1 -- Change the database recovery model to SIMPLE. ALTER DATABASE [?] SET RECOVERY SIMPLE; -- Shrink the truncated log file to 1 MB. DBCC SHRINKFILE (@FileName, 1); END '
Thank you!
Author: Vincent Fenoll – SQL Server DBA Montreal