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:

1. Call stored procedure sp_MSForeachdb
2. Display current db for information
3. Set variable to transaction log name
4. Change the database recovery model to SIMPLE for the database
5. 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