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

 

Execute a Command in the Context of Each Database in SQL Server - Part 2


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

Problem

In my last tip, I introduced a new "for each db" procedure that runs a command in the context of each database, rather than just swapping the database name into each placeholder within a command. This intro procedure was barebones, without all the options from a more reliable and flexible sp_MSforeachdb. Combining this with some of the changes made to the version in Brent Ozar Unlimited’s First Responder Kit, and a few improvements of my own, I wanted to follow up with a more complete version.

Solution

I’ll post the new version in its entirety below, but I wanted to start by going over changes to the parameter list.

@replace_character nchar(1) = N'?'

Unchanged. But I wanted to highlight this one because, even though the purpose of this stored procedure is to execute in each database’s context, there may still be a need to inject the database name in the command. So you can still use a command that includes the ? and replaces that token with each database name. Better examples later, but for now let’s just look at a very simple example:

EXEC dbo.sp_ineachdb @command = N'SELECT db = ''?'', COUNT(*) FROM sys.objects;';

Results:

Results from including a replacement token

Of course, if you don’t like the [square brackets] around the database name in the output, you can always use @suppress_quotename = 1, but use caution in combination with a replacement token: if you have any poorly-named databases, your command won’t run correctly, since a syntax error will occur.

@select_dbname bit = 0

New. To complement @print_dbname, you can use this parameter if you want the name of the DB output as a resultset interspersed with the resultsets from the command itself. For example:

EXEC dbo.sp_ineachdb 
    @command = N'SELECT COUNT(*) FROM sys.objects;',
    @select_dbname = 1;

Result:

Results using @select_dbname = 1

@print_command bit = 0

New. In sp_foreachdb, your options were either to only execute the commands, or only print them. Now you can print them in addition to executing them, so you can validate what was executed (up to the limits of the PRINT command, of course).

@exclude_list nvarchar(max) = NULL

New. From the First Responder Kit, this comma-separated list of database names is used to exclude from execution. Important note: @exclude_list trumps @include_list.

@state_desc nvarchar(120) = N'ONLINE'

Use caution. Since we’re executing some command in a database’s context, it kind of has to be online to do so. However, there may be a need to print commands, say, to set all of the offline databases back to online. If you need to do this, make sure you set the @print_command_only = 1. Example:

EXEC dbo.sp_ineachdb 
  @command            = N'ALTER DATABASE ? SET ONLINE;',
  @state_desc         = N'OFFLINE',
  @print_command_only = 1;

For each offline database, the messages pane will show this:

/* For [offlinedb]: */
ALTER DATABASE [offlinedb] SET ONLINE;

If you exclude the @print_command_only parameter, you will see this message for each offline database, which should not be shocking:

Could not run against offlinedb : Database 'offlinedb' cannot be opened because it is offline..
Msg 50000, Level 1, State 0

@user_access nvarchar(128) = NULL

New. Allows you to execute a command only against databases that are in a specific user access mode (SINGLE_USER, MULTI_USER, RESTRICTED_USER). Let’s say you want to set all SINGLE_USER databases to MULTI_USER, you could do this:

EXEC dbo.sp_ineachdb 
  @command = N'ALTER DATABASE ? SET MULTI_USER WITH ROLLBACK IMMEDIATE;',
  @user_access = N'SINGLE_USER';

Other Improvements

The biggest improvement I’ve made, I believe, is the elimination of a nested INSERT … EXEC. In sp_foreachdb, the set of databases that the command should run against, based on the combination of parameters passed in, is built up using dynamic SQL. If a certain parameter is passed in, add a where clause dynamically, and append it. Then when the statement is built, INSERT … EXEC sys.sp_executesql. This isn’t a problem, really, except that if you then tried to INSERT … EXEC the output of sp_foreachdb into a #temp table, so that you could get all of the results in a single resultset:

CREATE TABLE #dbs(name sysname, c int);
INSERT #dbs EXEC dbo.sp_foreachdb @command = N'SELECT DB_NAME(), COUNT(*) FROM sys.objects;';
SELECT * FROM #dbs;
GO
DROP TABLE #dbs;

When you try that:

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

I wrote sp_ineachdb in such a way that it avoids all of this, primarily to avoid dynamic SQL and any potential nested exec problems. I used the opposite process: I build up the list of databases first, and then I pick away at them based on the parameters passed to the stored procedure. You can see this by changing the above command slightly and observing that it works without issue:

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

I also made better use of built-in functions like PARSENAME() and QUOTENAME(). In sp_foreachdb, there is some manual adding and parsing away of square brackets. This is not only messy, but it leads to failures on certain "bad" names, like CREATE DATABASE [who]]'s that]. sp_ineachdb handles all of these bad identifiers properly; if your database has a leading space, trailing space, quotes, apostrophes, dashes, starts with a number, or is a reserved word – your command should still work, as long as you don’t mix a token with suppressing quotename. Here are some examples that all work fine in sp_ineachdb:

CREATE DATABASE [456];
CREATE DATABASE [who-'what'456];
CREATE DATABASE [x456]]];  -- breaks sp_foreachdb
CREATE DATABASE [ leading space 456];
CREATE DATABASE [trailing space 456 ];
CREATE DATABASE [bad"name456];
CREATE DATABASE [bad.name456];
CREATE DATABASE [bad-name456];
CREATE DATABASE [bad]][[.name456];
CREATE DATABASE [bad[]]name456];
CREATE DATABASE [bad name456];

Now, you could argue that if you have a database name like [what]]on]]earth], you deserve what you get. I tend to err on the side of I don’t know what kind of crazy systems a reader has inherited from some long-gone disgruntled employee. So I like to make things as resilient as they can be.

The Code

This is a long one, so roll up your sleeves. I tried to intersperse comments at appropriate points, but if you have any issues about how it works, please leave a comment below.

CREATE PROCEDURE dbo.sp_ineachdb
  @command             nvarchar(max),
  @replace_character   nchar(1) = N'?',
  @print_dbname        bit = 0,
  @select_dbname       bit = 0, -- new
  @print_command       bit = 0, -- new
  @print_command_only  bit = 0,
  @suppress_quotename  bit = 0, -- use with caution
  @system_only         bit = 0,
  @user_only           bit = 0,
  @name_pattern        nvarchar(300)  = N'%', 
  @database_list       nvarchar(max)  = NULL,
  @exclude_list        nvarchar(max)  = NULL, -- from First Responder Kit
  @recovery_model_desc nvarchar(120)  = NULL,
  @compatibility_level tinyint        = NULL,
  @state_desc          nvarchar(120)  = N'ONLINE',
  @is_read_only        bit = 0,
  @is_auto_close_on    bit = NULL,
  @is_auto_shrink_on   bit = NULL,
  @is_broker_enabled   bit = NULL,
  @user_access         nvarchar(128)  = NULL  -- new
-- WITH EXECUTE AS OWNER – maybe not a great idea, depending on the security your system
AS
BEGIN
  SET NOCOUNT ON;
  DECLARE @exec   nvarchar(150),
          @sx     nvarchar(18) = N'.sys.sp_executesql',
          @db     sysname,
          @dbq    sysname,
          @cmd    nvarchar(max),
          @thisdb sysname,
          @cr     char(2) = CHAR(13) + CHAR(10);
  CREATE TABLE #ineachdb(id int, name nvarchar(512));
  IF @database_list > N''
  -- comma-separated list of potentially valid/invalid/quoted/unquoted names
  BEGIN
    ;WITH n(n) AS (SELECT 1 UNION ALL SELECT n+1 FROM n WHERE n < 4000),
    names AS
    (
      SELECT name = LTRIM(RTRIM(PARSENAME(SUBSTRING(@database_list, n, 
        CHARINDEX(N',', @database_list + N',', n) - n), 1)))
      FROM n WHERE n <= LEN(@database_list)
        AND SUBSTRING(N',' + @database_list, n, 1) = N','
    ) 
    INSERT #ineachdb(id,name) SELECT d.database_id, d.name
    FROM sys.databases AS d
    WHERE EXISTS (SELECT 1 FROM names WHERE name = d.name)
    OPTION (MAXRECURSION 0);
  END
  ELSE
  BEGIN
    INSERT #ineachdb SELECT database_id, name FROM sys.databases;
  END
  -- first, let's delete any that have been explicitly excluded
  IF @exclude_list > N'' 
  -- comma-separated list of potentially valid/invalid/quoted/unquoted names
  -- exclude trumps include
  BEGIN
    ;WITH n(n) AS (SELECT 1 UNION ALL SELECT n+1 FROM n WHERE n < 4000),
    names AS
    (
      SELECT name = LTRIM(RTRIM(PARSENAME(SUBSTRING(@exclude_list, n, 
        CHARINDEX(N',', @exclude_list + N',', n) - n), 1)))
      FROM n WHERE n <= LEN(@exclude_list)
          AND SUBSTRING(N',' + @exclude_list, n, 1) = N','
    )
    DELETE d FROM #ineachdb AS d
    INNER JOIN names
    ON names.name = d.name
    OPTION (MAXRECURSION 0);
  END
  -- next, let's delete any that *don't* match various criteria passed in
  DELETE dbs FROM #ineachdb AS dbs
  WHERE (@system_only = 1 AND id NOT IN (1,2,3,4))
     OR (@user_only   = 1 AND id     IN (1,2,3,4))
     OR name NOT LIKE @name_pattern
     OR EXISTS
     (
       SELECT 1 FROM sys.databases AS d
       WHERE d.database_id = dbs.id
       AND NOT
       (
         recovery_model_desc     = COALESCE(@recovery_model_desc, recovery_model_desc)
         AND compatibility_level = COALESCE(@compatibility_level, compatibility_level)
         AND is_read_only        = COALESCE(@is_read_only, is_read_only)
         AND is_auto_close_on    = COALESCE(@is_auto_close_on, is_auto_close_on)
         AND is_auto_shrink_on   = COALESCE(@is_auto_shrink_on, is_auto_shrink_on)
         AND is_broker_enabled   = COALESCE(@is_broker_enabled, is_broker_enabled)
       )
     );
  -- if a user access is specified, remove any that are NOT in that state
  IF @user_access IN (N'SINGLE_USER', N'MULTI_USER', N'RESTRICTED_USER')
  BEGIN
    DELETE #ineachdb WHERE 
      CONVERT(nvarchar(128), DATABASEPROPERTYEX(name, 'UserAccess')) <> @user_access;
  END  
  -- finally, remove any that are not *fully* online or we can't access
  DELETE dbs FROM #ineachdb AS dbs
  WHERE EXISTS
  (
    SELECT 1 FROM sys.databases
    WHERE database_id = dbs.id
    AND
    ( 
      @state_desc = N'ONLINE' AND
      (
        [state] & 992 <> 0  -- inaccessible
        OR state_desc <> N'ONLINE' -- not online
        OR HAS_DBACCESS(name) = 0  -- don't have access
        OR DATABASEPROPERTYEX(name, 'Collation') IS NULL -- not fully online. See "status" here:
        -- https://docs.microsoft.com/en-us/sql/t-sql/functions/databasepropertyex-transact-sql
      )
      OR (@state_desc <> N'ONLINE' AND state_desc <> @state_desc)
      OR
      (
        -- from Andy Mallon / First Responders Kit. Make sure that if we're an 
        -- AG secondary, we skip any database where allow connections is off
        SERVERPROPERTY('IsHadrEnabled') = 1
        AND EXISTS
        (
          SELECT 1 FROM sys.dm_hadr_database_replica_states AS drs 
            INNER JOIN sys.availability_replicas AS ar
            ON ar.replica_id = drs.replica_id
            INNER JOIN sys.dm_hadr_availability_group_states ags 
            ON ags.group_id = ar.group_id
            WHERE drs.database_id = dbs.id
            AND ar.secondary_role_allow_connections = 0
            AND ags.primary_replica <> @@SERVERNAME
        )
      )
    )
  );
  -- Well, if we deleted them all...
  IF NOT EXISTS (SELECT 1 FROM #ineachdb)
  BEGIN
    RAISERROR(N'No databases to process.', 1, 0);
    RETURN;
  END
  -- ok, now, let's go through what we have left
  DECLARE dbs CURSOR LOCAL FAST_FORWARD
    FOR SELECT DB_NAME(id), QUOTENAME(DB_NAME(id))
    FROM #ineachdb;
  OPEN dbs;
  FETCH NEXT FROM dbs INTO @db, @dbq;
  DECLARE @msg1 nvarchar(512) = N'Could not run against %s : %s.',
          @msg2 nvarchar(max);
  WHILE @@FETCH_STATUS <> -1
  BEGIN
    SET @thisdb = CASE WHEN @suppress_quotename = 1 THEN @db ELSE @dbq END;
    SET @cmd = REPLACE(@command, @replace_character, REPLACE(@thisdb,'''',''''''));
    BEGIN TRY
      IF @print_dbname = 1
      BEGIN
        PRINT N'/* ' + @thisdb + N' */';
      END
      IF @select_dbname = 1
      BEGIN
        SELECT [ineachdb current database] = @thisdb;
      END
      IF 1 IN (@print_command, @print_command_only)
      BEGIN
        PRINT N'/* For ' + @thisdb + ': */' + @cr + @cr + @cmd + @cr + @cr;
      END
      IF COALESCE(@print_command_only,0) = 0
      BEGIN
        SET @exec = @dbq + @sx;
        EXEC @exec @cmd;
      END
    END TRY
    BEGIN CATCH
      SET @msg2 = ERROR_MESSAGE();
      RAISERROR(@msg1, 1, 0, @db, @msg2);
    END CATCH
    FETCH NEXT FROM dbs INTO @db, @dbq;
  END
  CLOSE dbs; DEALLOCATE dbs;
END
GO
EXEC sys.sp_MS_marksystemobject N'sp_ineachdb';

Summary

This stored procedure offers a number of advantages over sp_foreachdb, and I hope you find it useful. It should be added to the First Responder Kit soon, too, so watch out for that.

Next Steps

Read on for related tips and other resources:



Last Updated: 2018-10-22


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.



    



Friday, March 08, 2019 - 9:31:19 AM - Aaron Bertrand Back To Top

Ahmed, yes, sorry, should have noted it is designed (and only tested) for 2012+. Especially since 2008 goes completely unsupported this year. If you need to continue using SQL Server 2008, just take out the clause Andy Mallon added about Availability Groups.


Thursday, March 07, 2019 - 5:46:28 PM - Ahmed Rouani Back To Top

Not working on 2008 version need a small adjustment

DELETE dbs FROM #ineachdb AS dbs
  WHERE EXISTS
  (
    SELECT 1 FROM sys.databases
    WHERE database_id = dbs.id
    AND
    ( 
      @state_desc = N'ONLINE' AND
      (
        [state] & 992 <> 0  -- inaccessible
        OR state_desc <> N'ONLINE' -- not online
        OR HAS_DBACCESS(name) = 0  -- don't have access
        OR DATABASEPROPERTYEX(name, 'Collation') IS NULL -- not fully online. See "status" here:
        -- https://docs.microsoft.com/en-us/sql/t-sql/functions/databasepropertyex-transact-sql
      )
      OR (@state_desc <> N'ONLINE' AND state_desc <> @state_desc)
      
      
    )
  );
 
 
if @@VERSION  not like '%Microsoft SQL Server 2005%' and @@VERSION not like '%Microsoft SQL Server 2008%' and @@VERSION  not like '%Microsoft SQL Server 2008 R2%' 
BEGIN
 
DELETE dbs FROM #ineachdb AS dbs
  WHERE EXISTS
  (
    SELECT 1 FROM sys.databases
    WHERE database_id = dbs.id
    AND
   
        SERVERPROPERTY('IsHadrEnabled') = 1 
        AND EXISTS
        (
          SELECT 1 FROM sys.dm_hadr_database_replica_states AS drs 
            INNER JOIN sys.availability_replicas AS ar
            ON ar.replica_id = drs.replica_id
            INNER JOIN sys.dm_hadr_availability_group_states ags 
            ON ags.group_id = ar.group_id
            WHERE drs.database_id = dbs.id
            AND ar.secondary_role_allow_connections = 0
            AND ags.primary_replica <> @@SERVERNAME
        )
      
  )
END

Learn more about SQL Server tools