Sql Server loop through databases

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.

loop all databases sql server

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''
-- 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.

-- Shrink the truncated log file to 1 MB.


Thank you!

Author: Vincent Fenoll – SQL Server DBA Montreal

Laisser un commentaire

Votre adresse e-mail ne sera pas publiée. Les champs obligatoires sont indiqués avec *

Ce site utilise Akismet pour réduire les indésirables. En savoir plus sur comment les données de vos commentaires sont utilisées.