John McCormack DBA

SQL Server Databases and Cloud

An alternative to sp_msforeachdb

By Leave a Comment

It’s well known that the sp_msforeachdb stored procedure is undocumented however it can be handy. The trouble with the proc is that it is unreliable. What’s more, it could be dropped from future SQL Server releases causing all your automated scripts to fail.

This little script shows you a more reliable solution and saves you from relying on an undocumented procedure.

USE Master
SET NOCOUNT ON
IF OBJECT_ID('tempdb..#tmp') IS NOT NULL DROP TABLE #tmp

SELECT name,0 as completed
INTO #tmp
FROM sys.databases
WHERE name NOT IN('tempdb','Training2012') -- A list of DBs you don't wish to include

DECLARE @dbname sysname
DECLARE @cmd NVARCHAR(4000)

WHILE EXISTS(SELECT 1 FROM #tmp WHERE completed = 0)
BEGIN

SET @dbname = (SELECT TOP 1 NAME FROM #tmp WHERE completed = 0) -- You can ORDER BY name if you care about the order
SET @cmd = 'BACKUP DATABASE '+@dbname+' TO DISK = ''D:\Backup\'+@dbname+'.Bak'''
EXEC sp_executesql @cmd
UPDATE #tmp SET completed = 1 WHERE name = @dbname

END

Let’s break up what the script actually does:

  1. DROP temp table if it already exists
    1. [sql]IF OBJECT_ID(‘tempdb..#tmp’) IS NOT NULL DROP TABLE #tmp[/sql]
  2. Select into a temporary table with two columns. (Name and Completed). We set completed=0 for each row created.
    1. [sql]SELECT name,0 as completed INTO #tmp FROM sys.databases[/sql]
  3. The WHERE clause is optional
    1. [sql]WHERE name NOT IN(‘tempdb’,’Training2012′)[/sql]
  4. Create a @dbname variable (for each DB name) and a @cmd variable (for building up the T-SQL command).
    1. [sql]DECLARE @dbname sysname
      DECLARE @cmd NVARCHAR(4000)[/sql]
  5. The LOOP (Create and execute a new statement for each DB name and exit the loop once all DBs have been handled).
    1. [sql]WHILE EXISTS (SELECT 1 FROM #tmp WHERE completed = 0)
      BEGIN
      SET @dbname = (SELECT TOP 1 NAME FROM #tmp WHERE completed = 0) — You can ORDER BY name if you care about the order
      SET @cmd = ‘BACKUP DATABASE ‘+@dbname+’ TO DISK = ”D:\Backup\’+@dbname+’.Bak”’
      EXEC sp_executesql @cmd
      UPDATE #tmp SET completed = 1 WHERE name = @dbname
      END[/sql]

This approach is simple and reliable and it avoids the use of undocumented stored procedures and cursors. The example above demonstrates a native backup but this command can be anything you like.