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 initial version that was 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:
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:
@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.<strong>sp_ineachdb</strong> @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 is now available in the First Responder Kit, replacing sp_foreachdb.
Next Steps
Read on for related tips and other resources:
- Execute a Command in the Context of Each Database in SQL Server
- BrentOzarULTD / SQL-Server-First-Responder-Kit
- Making a more reliable and flexible sp_MSforeachdb
- Run The Same SQL Command Against All SQL Server Databases
- Iterating through SQL Server databases and database objects without cursors
- Common uses for sp_MSforeachdb
