John McCormack DBA

SQL Server Databases and Cloud

  • Personal
    • About
  • Free Training
    • SQL Server on Amazon RDS (Free Course)
    • Free practice questions to help you pass DP-900
  • Save money in Azure
    • Azure IaaS SQL Backups – Stop burning money
    • Your Azure SQL Database and Managed Instance is too big
    • Turn the cloud off at bedtime to save 70%
    • Your Azure SQL Virtual Machine might be too big
    • Save money with Azure SQL DB serverless
    • Save up to 73% with reserved instances
    • Delete unused instances to save money in Azure
  • Hire me
    • 60 minute cost optimization
    • Let me solve your SQL Server problems
    • Take a look at my Sessionize speaker’s profile

An alternative to sp_msforeachdb

23rd December 2015 By John McCormack 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.

 

Filed Under: front-page, T-SQL Tagged With: sp_msforeachdb, sql, SQL server, t-sql, undocumented stored procedure

Privacy & Cookies: This site uses cookies. By continuing to use this website, you agree to their use.
To find out more, including how to control cookies, see here: Cookie Policy

John McCormack · Copyright © 2023