Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 
Failover Clustering in the Cloud - Understanding Your Options - Free Webinar
 

Execute a Command in the Context of Each Database in SQL Server using sp_ineachdb


By:   |   Last Updated: 2018-10-04   |   Comments (2)   |   Related Tips: More > T-SQL

Problem

In a previous tip, I showed you how to create a more reliable and flexible sp_MSforeachdb. This replacement avoids the problems inherent in the native solution where it skips databases, fails against databases with problematic identifiers (like CREATE DATABASE [1db'-.foo];), and adds options (like only execute against system databases, databases in a certain recovery model, or databases matching a certain name pattern). The title of this tip sounds a lot like a duplicate, but it is not.

Recently, a question came up about why it doesn’t execute the command in the actual context of the database; the example was that SELECT DB_NAME() reflects the name of the calling database over and over again, rather than the database where the command was expected to run. Many people assume that it should issue a USE currentdb; command for you, but that’s not how it works, and so a function like DB_NAME() will only ever run in the context of the caller.

Solution

My replacement works like this because I wanted to exactly mimic the behavior of the native system procedure. It takes your command, loops through each database, replaces every instance of ? in the command with the current database name, and executes. The same symptom will happen if you run the following command:

USE some_database;
GO
EXEC dbo.sp_foreachdb N'SELECT COUNT(*) FROM sys.objects;';

You will get the same result every time. Why? Because it will count the objects in some_database every time. In order to make it look at the other databases, you have to include the replacement character (?):

USE some_database;
GO
EXEC dbo.sp_foreachdb N'SELECT COUNT(*) FROM ?.sys.objects;';

But it doesn’t have to be this way. We can also create a stored procedure that actually executes your command – without any replacement – in the context of each database, without having to add all the instances of ? yourself, or manually adding USE [?]; to the beginning of each command. Both of these can be troublesome if you are generating scripts or deriving them from a source and hoping to execute them without manipulation.

And so, I present a stored procedure that does this, with a working title of sp_ineachdb. The name reflects that it executes a command in the context of each database, instead of simply for each database. For now, I’ll leave out all the options I added to the original replacement:

USE [master];
GO

CREATE PROCEDURE dbo.sp_ineachdb
  @command nvarchar(max)
AS
BEGIN
  DECLARE @context nvarchar(150),
          @sx      nvarchar(18) = N'.sys.sp_executesql',
          @db      sysname;
  CREATE TABLE #dbs(name sysname PRIMARY KEY);

  INSERT #dbs(name) SELECT QUOTENAME(name) 
    FROM sys.databases
    WHERE [state] & 992 = 0 - accessible
    AND DATABASEPROPERTYEX(name, 'UserAccess') <> 'SINGLE_USER' 
    AND HAS_DBACCESS(name) = 1;

  DECLARE dbs CURSOR LOCAL FAST_FORWARD
    FOR SELECT name, name + @sx FROM #dbs;

  OPEN dbs;

  FETCH NEXT FROM dbs INTO @db, @context;

  DECLARE @msg nvarchar(512) = N'Could not run against %s : %s.',
          @err nvarchar(max);

  WHILE @@FETCH_STATUS <> -1
  BEGIN

    BEGIN TRY
      EXEC @context @command = @command;
    END TRY
    BEGIN CATCH
      SET @err = ERROR_MESSAGE();
      RAISERROR(@msg, 1, 0, @db, @err);
    END CATCH

    FETCH NEXT FROM dbs INTO @db, @context;
  END

  CLOSE dbs; DEALLOCATE dbs;

END
GO

EXEC sys.sp_MS_marksystemobject N'sp_ineachdb';	

Stepping through the code, here’s what it does:

  • Creates a table to hold the database names
  • Inserts all the databases that are accessible, online, not single_user, and that the caller can access
  • Creates a cursor to step through each name
  • For each database:
    • Tries to execute the command in each database (I’ll explain how that works in a moment)
    • If it fails, raise a non-batch-aborting error (essentially a print stating what happened.

That’s it. Now, how the execution works, is that we can execute in the context of another database (or server even) by creating a string that represents [location].sys.sp_executesql. For example:

USE [master];
GO

DECLARE @context nvarchar(150) = N'tempdb.sys.sp_executesql';
EXEC @context @command = N'PRINT DB_NAME();';

What this is really doing is:

EXEC tempdb.sys.sp_executesql @command = N'PRINT DB_NAME();';

This executes the print statement in the context of tempdb, without ever having to switch to tempdb. In sp_ineachdb, we’re simply taking advantage of this by building @context dynamically. The QUOTENAME() protects you from bad identifiers, so go ahead and try this with a bunch of poorly-named databases:

CREATE DATABASE [12345];
CREATE DATABASE [1db'-.foo];
CREATE DATABASE [bad"name];
CREATE DATABASE [bad.name];
CREATE DATABASE [bad-name];
CREATE DATABASE [bad]][[.name];
CREATE DATABASE [bad[]]name];
CREATE DATABASE [bad name];
CREATE DATABASE [ leading space];
CREATE DATABASE [trailing space ];

These all work no problem:

EXEC dbo.sp_ineachdb @command = N'PRINT DB_NAME();';

Output:

 leading space
12345
1db'-.foo
bad name
bad"name
bad.name
bad[]name
bad][[.name
bad-name
master
model
msdb
tempdb
trailing space

This means that if you do want the calling context to mean something (say, you have a static, central table you want to join against everything else in all the other databases, you need to qualify the calling context in the command. As an example, you want to find all the objects with names that match a central list, which you store in a table in a database called Central.

CREATE TABLE dbo.ObjectNameBlacklist
(
  badname sysname
);

INSERT dbo.ObjectNameBlacklist(badname) VALUES(N'badword');

With sp_foreachdb, you would say:

EXEC dbo.sp_foreachdb N'SELECT ''?'', badname 
  FROM dbo.ObjectNameBlacklist AS onb
  WHERE EXISTS
  (
    SELECT 1 FROM ?.sys.objects WHERE name = onb.name COLLATE DATABASE_DEFAULT
  );';

(And admittedly you would get an error on at least one of the bad database names from above.)

With the new procedure, you would say (differences bolded):

EXEC dbo.sp_ineachdb N'SELECT DB_NAME(), badname 
  FROM Central.dbo.ObjectNameBlacklist AS onb
  WHERE EXISTS
  (
    SELECT 1 FROM sys.objects WHERE name = onb.name COLLATE DATABASE_DEFAULT
  );';	

This one does not fail on any of the bad database names, showing how much more resilient QUOTENAME() is compared to manually constructing your own quoted identifiers. More importantly, you can now pass commands without worrying about ?, how to escape it, or adding USE commands.

Another nice benefit is that nested INSERT … EXEC works. With sp_foreachdb, if you try this:

CREATE TABLE #c(db sysname, c int);
INSERT #c EXEC dbo.sp_foreachdb N'SELECT ''?'', COUNT(*) FROM ?.sys.objects;';
SELECT db,c FROM #c;
GO
DROP TABLE #c;

The result is an error message:

Msg 8164, Level 16, State 1, Procedure dbo.sp_foreachdb, Line 87 [Batch Start Line 0]
An INSERT EXEC statement cannot be nested.

With at least this simple version of sp_ineachdb, it works, because there isn’t a nested INSERT … EXEC

CREATE TABLE #c(db sysname, c int);
INSERT #c EXEC dbo.sp_ineachdb N'SELECT DB_NAME(), COUNT(*) FROM sys.objects;';
SELECT db,c FROM #c;
GO
DROP TABLE #c;	

Summary

It is definitely possible to look at the “execute the same command against a bunch of databases” problem in a variety of ways. This stored procedure shows how to execute the command in each database’s context, rather than expect the command to provide tokens to slip the database name into. Depending on what you need to do and where the commands are coming from, this might be a better option, as it overcomes multiple limitations of both the native procedure and my replacement.

You can easily extend this solution by adding the other options I added for sp_foreachdb, to better dictate which databases to execute against. I didn’t do that here because I wanted to simplify the above to demonstrate how it works; I will address that in a follow-up tip (and will try to find a way to resolve the nested INSERT … EXEC issue).

Next Steps

Read on for related tips and other resources:



Last Updated: 2018-10-04


next webcast button


next tip button



About the author
MSSQLTips author Aaron Bertrand Aaron Bertrand (@AaronBertrand) is a Product Manager at SentryOne, with industry experience dating back to Classic ASP and SQL Server 6.5. He is editor-in-chief of the performance-related blog, SQLPerformance.com, and serves as a community moderator for the Database Administrators Stack Exchange.

View all my tips





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Saturday, December 01, 2018 - 10:21:04 AM - Aaron Bertrand Back To Top

Hey Robert, neither of those were my ideas. They came from these lines in sp_MSforeachdb, and pre-date sys.databases:

   N' where (d.status & ' + @inaccessible + N' = 0)' +  -- my comment: this @inaccessible resolves to 992
   N' and (DATABASEPROPERTYEX(d.name, ''UserAccess'') <> ''SINGLE_USER'' and (has_dbaccess(d.name) = 1))' )  

I could have simplified one or both, sure, but I wanted to stay as true to the parts of the original that work as I could (while tossing the parts that don't work).


Saturday, December 01, 2018 - 6:22:36 AM - Robert Steen Back To Top

Could you please clarify 2 things to do with the querying of sys.databases:

1. Why mask "state"? According to BOL, it is a tinyint with a sequential value, not a bitmask.

2. Why use "DATABASEPROPERTYEX(name, 'UserAccess') <> 'SINGLE_USER'", when "user_access" is available in sys.databases?

But, in any case, very nice. Thank you.


Learn more about SQL Server tools