Something that we are all keenly aware of is that (generally) the use of a CURSOR in T-SQL is generally bad. A CURSOR is an iterative based method, and these generally perform very poorly in SQL Server, which is far better suited to set-based methods. sp_msforeachdb is an undocumented Special Procedure, so one day it may be removed (unannounced), but it also uses a CURSOR and can be unreliable. Something, like Aaron Bertrand did with sp_foreachdb, I have designed my own with some extra features.
The Objects
Let's get to the interesting part first, the Objects. There are 2 parts to this (which I suggest making in the master database, as we'll set the Procedure to be a system procedure later), one is a table object for optionally passing a list of databases and the other the actual Procedure.
objectlist Table Type
Rather simple this:
USE master;
GO
IF NOT EXISTS (SELECT 1 FROM sys.types WHERE [name] = N'objectlist')
CREATE TYPE dbo.objectlist AS table ([name] sysname);sp_foreachdatabase Stored Procedure
USE master;
GO
CREATE OR ALTER PROC [sp].[foreachdatabase] @Command nvarchar(MAX),
@Delimit_Character nchar(1) = N'?', --Character to be replaced with a delimit identified version of the datbaase name I.e. [master]
@Quote_Character nchar(1) = N'&', --Character to be replaced with a single quoted (') version of the datbaase name I.e. 'master'
@Skip_System bit = 0, --Omits master, msdb, tempdb and model. Ignored if @Database_List has data.
@Skip_User bit = 0, --Omits all user databases. Ignored if @Database_List has data.
@Skip_ReadOnly bit = 0, --Omits databases in Read-Only state. Ignored if @Database_List has data.
@Database_List dbo.objectlist READONLY, --If @Skip_System and @Skip_User equal 1, and this is empty, an error will be thrown
@Auto_Use bit = 0, --Automatically starts each command against a database with a USE
@Exit_On_Error bit = 1, --If an error is occurs against a single database, the command will still be run against the remainder. Otherwise everything is rolled back
--This does not effect the @Pre_Command and @Post_Command statements
@Pre_Command nvarchar(MAX) = NULL, --Command to run before @Command. Does not use Character Replacements. Run against master DB.
@Post_Command nvarchar(MAX) = NULL, --Command to run after @Command. Does not use Character Replacements. Run against master DB.
@Command_Run nvarchar(MAX) = NULL OUTPUT, --Returns the generated and replaced command, for trouble shooting
@WhatIf bit = 0 --Don't actually run the statements; @Command_Run will still return the batch that would have been run
/*
Written by Thom A 2019-11-26
Original Source: https://wp.larnu.uk/a-cursor-free-version-of-sp_msforeachdb/
Licenced under CC BY-ND 4.0
*/
AS BEGIN
--Do some checking of passed values first
--Check that @Skip_System, @Skip_User aren't both 0 or that @Database_List has some rows
IF (@Skip_System = 1 AND @Skip_User = 1 AND NOT EXISTS (SELECT 1 FROM @Database_List))
THROW 62401, N'System and User databases cannot be skipped if a Database List is not supplied.', 16;
IF @Delimit_Character IS NULL OR @Delimit_Character = ''
THROW 62402, N'@Delimit_Character cannot have a value of NULL or ''''.', 16;
IF @Quote_Character IS NULL OR @Quote_Character = ''
THROW 62403, N'@Quote_Character cannot have a value of NULL or ''''.', 16;
IF @Skip_User IS NULL
THROW 62404, N'@Skip_User cannot have a value of NULL.', 16;
IF @Skip_System IS NULL
THROW 62405, N'@Skip_System cannot have a value of NULL.', 16;
IF @Auto_Use IS NULL
PRINT N'@Auto_Use has a value of NULL. Behaviour will be as if the value is 0.';
IF @WhatIf IS NULL
PRINT N'@WhatIf has a value of NULL. Behaviour will be as if the value is 0.';
DECLARE @CRLF nchar(2) = NCHAR(13) + NCHAR(10);
DECLARE @RC int;
--Add the Pre Command to the batch
SET @Command_Run = ISNULL(N'/* --- Pre Command Begin. --- */' + @CRLF + @CRLF + N'USE master;' + @CRLF + @CRLF + @Pre_Command + @CRLF + @CRLF + N'/* --- Pre Command End. --- */', N'');
--Get the databases we need to deal with
--As @Database_List might be empty and it's READONLY, and we're going to do the command in database_id order we need another variable.
DECLARE @DBs table (database_id int,
database_name sysname);
IF EXISTS (SELECT 1 FROM @Database_List)
INSERT INTO @DBs (database_id,database_name)
SELECT d.database_id,
d.[name]
FROM sys.databases d
JOIN @Database_List DL ON d.[name] = DL.[name]
WHERE d.state_desc != 'OFFLINE';
ELSE
INSERT INTO @DBs (database_id,database_name)
SELECT d.database_id,
d.[name]
FROM sys.databases d
WHERE ((d.database_id <= 4 AND @Skip_System = 0) OR (d.database_id > 4 AND @Skip_User = 0))
AND (@Skip_ReadOnly = 0 OR (@Skip_ReadOnly = 1 AND d.is_read_only = 0))
AND d.state_desc != 'OFFLINE'
OPTION (RECOMPILE);
SET @Command_Run = @Command_Run + @CRLF + @CRLF +
N'/* --- Begin command for each database. --- */' + @CRLF + @CRLF +
CASE WHEN @Exit_On_Error = 0 THEN N'--Turning XACT_ABORT off due to @Exit_On_Error parameter' + @CRLF + @CRLF + N'SET XACT_ABORT OFF;' + @CRLF + N'DECLARE @Error nvarchar(4000);' ELSE N'SET XACT_ABORT ON;' END +
(SELECT @CRLF + @CRLF +
N'/* --- Running @Command against database ' + QUOTENAME(DB.database_name,'''') + N'. --- */' + @CRLF + @CRLF +
CASE WHEN @Auto_Use = 1 THEN N'USE ' + QUOTENAME(DB.database_name) + N';' + @CRLF + @CRLF ELSE N'' END +
N'BEGIN TRY' + @CRLF + @CRLF +
REPLACE(REPLACE(@Command, @Delimit_Character, QUOTENAME(DB.database_name)),@Quote_Character, 'N' + QUOTENAME(DB.database_name,'''')) + @CRLF + @CRLF +
'END TRY' + @CRLF +
N'BEGIN CATCH' + @CRLF +
CASE WHEN @Exit_On_Error = 0 THEN N' SET @Error = N''The following error occured during the batch, but has been skipped:'' + NCHAR(13) + NCHAR(10) + ' + @CRLF +
N' N''Msg '' + CONVERT(nvarchar(6),ERROR_NUMBER()) + '', Level '' + CONVERT(nvarchar(6),ERROR_SEVERITY()) + '', State '' + CONVERT(nvarchar(6),ERROR_STATE()) + '', Line '' + CONVERT(nvarchar(6),ERROR_LINE()) + NCHAR(13) + NCHAR(10) +' + @CRLF +
N' ERROR_MESSAGE();' + @CRLF +
N' PRINT @Error;' + @CRLF +
N' SET @RC = ERROR_NUMBER();'
ELSE N' THROW;'
END + @CRLF +
N'END CATCH;' + @CRLF +
N'/* --- Completed @Command against database ' + QUOTENAME(DB.database_name,'''') + N'. --- */'
FROM @DBs DB
FOR XML PATH(N''),TYPE).value('.','nvarchar(MAX)') + @CRLF + @CRLF +
CASE WHEN @Exit_On_Error = 0 THEN N'--Turning XACT_ABORT back on due to @Exit_On_Error parameter' + @CRLF + @CRLF + N'SET XACT_ABORT ON;' ELSE N'' END;
SET @Command_Run = @Command_Run + ISNULL(@CRLF + @CRLF + N'/* --- Post Command Begin. --- */' + @CRLF + @CRLF + N'USE master;' + @CRLF + @CRLF + @Post_Command + @CRLF + @CRLF + N'/* --- Post Command End. --- */', N'');
IF @WhatIf = 0
EXEC sys.sp_executesql @Command_Run, N'@RC int OUTPUT', @RC = @RC;
ELSE
PRINT N'What if: see value returned from @Command_Run.';
SET @RC = ISNULL(@RC, 0);
RETURN @RC;
END;
GOI won't lie, at the moment it's a little messy, and I might/will improve the alignments at a later date. I also apologise if the colouring that the Syntax Highlighter I use provides for the above is poor. (I have changed to Prism, which works way better.) If you paste this into your preferred IDE I promise it'll look better.
If you do want to mark the object as a system object, then you can do so using the below:
USE master;
GO
EXEC sp_ms_marksystemobject 'sp_foreachdatabase';If you don't want to do this, I suggest removing the sp_ prefix from the object's name.
What it does
There was a few things I wanted to add to the existing functionality that sp_msforeachdb had, and the one by Aaron Bertrand. One thing I wanted to do was move away from a CURSOR completely, but still retain (some of) the functionality to not completely fail in the event of an error. One advantage that a Cursor would have is that each statement against a database would be run in it's own batch; meaning that if one failed the others wouldn't be effected. The @Exit_On_Error parameter adds this functionality by Catching the error and then printing it. The SP also returns the error number of the last error number returned. Note that a TRY...CATCH doesn't work for all types of errors (such as an invalid object name).
Another subject I wanted to address was the lack of proper quoting of the database name. Specifically both Delimit Identified ([master]) and Quote Identified ('master'). Although no one should should be foolish enough to have a database name with a ] or ' character in it, they do exist. As a there are 2 parameters for replacement in the SP. The first is the one we're all used to, called @Delimit_Character, and this character will be replaced by a delimit identified version of the database's name. The default value for this is ?, which is the same as sp_msforeachdb.
The new parameter is @Quote_Character, and this character will be replaced with a quote identified version of the database's name. The default value for this is &. This allows you to use syntax such as SELECT & AS DatabaseName;, and if your database is "silly" enough to have single quotes, the name will be handled gracefully.
I've also added a Pre and Post command to the Procedure, which will allow you to do some commands inside the same batch prior and after the statements that are run against each database. Both of these commands will be preceded by a USE master; statement, however, you can include your own in the command if you need it run in a different database's context.
I've also added a couple of other useful parameters. Firstly you have @Auto_Use, which will prefix each command against the database with a USE statement for that database. This defaults to 0, to mimic the behaviour of sp_msforeachdb. I've also added the @Skip_System and @Skip_User parameters, which respectively will not run the command against the system or user databases. If you, prefer, however, you can pass a list of the databases using the @Database_List parameter, which you can see the caveat on above.
Considerations
Because I use a custom datatype (dbo.objectlist) the Procedure will only work within the context of master; trying to run it from a different database will not work, and I appreciate that is a problem. As a result, after this article has been publish, I will be added a version that uses a delimited list of databases instead. Personally, I wanted to avoid that requirement (and I would hope that you don't even have to use this inside your own Stored Procedure as a regular task) but it's not something could avoid.
Like mentioned, as well, errors that aren't caught by a TRY...CATCH will propagate the error, regardless of the value of @Exit_On_Error. If you do have the potential, for example, to be referencing an object that won't exist I would suggest handling that in your Command instead.
Example Usage
This is an example, with the creation of some silly database names, to demonstrate both the ? and & replacement working.
USE master;
GO
--Create silly databases
CREATE DATABASE [Silly[DB]]Name];
CREATE DATABASE [Mc'Donald's_Farm];
CREATE DATABASE [123];
CREATE DATABASE ['I made a good decision'];
GO
USE master;
GO
DECLARE @CRLF nchar(2) = NCHAR(13) + NCHAR(10);
--Declare the commands
DECLARE @Pre_Command nvarchar(MAX) = N'CREATE TABLE #ObjectCount (DatabaseID int,' + @CRLF +
N' DatabaseName sysname,' + @CRLF +
N' Objects int);';
DECLARE @Command nvarchar(MAX) = N'INSERT INTO #ObjectCount (DatabaseID, DatabaseName, Objects)' + @CRLF +
N'SELECT DB_ID(&), &, COUNT(*)' + @CRLF +
N'FROM ?.sys.objects';
DECLARE @Post_Command nvarchar(MAX) = N'SELECT * FROM #ObjectCount;';
--OUTPUT parameter and RETURN value
DECLARE @Command_Run nvarchar(MAX),
@RC int;
--Run the commands
EXEC @RC = sp_foreachdatabase @Pre_Command = @Pre_Command, @Command = @Command, @Post_Command = @Post_Command, @Exit_On_Error = 0, @Command_Run = @Command_Run OUTPUT;
--Check the results from the OUTPUT and RETURN
SELECT @Command_Run, @RC;
GO
--Clean up
DROP DATABASE [Silly[DB]]Name];
DROP DATABASE [Mc'Donald's_Farm];
DROP DATABASE [123];
DROP DATABASE ['I made a good decision'];
And, if you're interested, the resulting command run, on one of my sandbox instances:
/* --- Pre Command Begin. --- */
USE master;
CREATE TABLE #ObjectCount (DatabaseID int,
DatabaseName sysname,
Objects int);
/* --- Pre Command End. --- */
/* --- Begin command for each database. --- */
--Turning XACT_ABORT off due to @Exit_On_Error parameter
SET XACT_ABORT OFF;
DECLARE @Error nvarchar(4000);
/* --- Running @Command against database 'master'. --- */
BEGIN TRY
INSERT INTO #ObjectCount (DatabaseID, DatabaseName, Objects)
SELECT DB_ID(N'master'), N'master', COUNT(*)
FROM [master].sys.objects
END TRY
BEGIN CATCH
SET @Error = N'The following error occured during the batch, but has been skipped:' + NCHAR(13) + NCHAR(10) +
N'Msg ' + CONVERT(nvarchar(6),ERROR_NUMBER()) + ', Level ' + CONVERT(nvarchar(6),ERROR_SEVERITY()) + ', State ' + CONVERT(nvarchar(6),ERROR_STATE()) + ', Line ' + CONVERT(nvarchar(6),ERROR_LINE()) + NCHAR(13) + NCHAR(10) +
ERROR_MESSAGE();
PRINT @Error;
SET @RC = ERROR_NUMBER();
END CATCH;
/* --- Completed @Command against database 'master'. --- */
/* --- Running @Command against database 'tempdb'. --- */
BEGIN TRY
INSERT INTO #ObjectCount (DatabaseID, DatabaseName, Objects)
SELECT DB_ID(N'tempdb'), N'tempdb', COUNT(*)
FROM [tempdb].sys.objects
END TRY
BEGIN CATCH
SET @Error = N'The following error occured during the batch, but has been skipped:' + NCHAR(13) + NCHAR(10) +
N'Msg ' + CONVERT(nvarchar(6),ERROR_NUMBER()) + ', Level ' + CONVERT(nvarchar(6),ERROR_SEVERITY()) + ', State ' + CONVERT(nvarchar(6),ERROR_STATE()) + ', Line ' + CONVERT(nvarchar(6),ERROR_LINE()) + NCHAR(13) + NCHAR(10) +
ERROR_MESSAGE();
PRINT @Error;
SET @RC = ERROR_NUMBER();
END CATCH;
/* --- Completed @Command against database 'tempdb'. --- */
/* --- Running @Command against database 'model'. --- */
BEGIN TRY
INSERT INTO #ObjectCount (DatabaseID, DatabaseName, Objects)
SELECT DB_ID(N'model'), N'model', COUNT(*)
FROM [model].sys.objects
END TRY
BEGIN CATCH
SET @Error = N'The following error occured during the batch, but has been skipped:' + NCHAR(13) + NCHAR(10) +
N'Msg ' + CONVERT(nvarchar(6),ERROR_NUMBER()) + ', Level ' + CONVERT(nvarchar(6),ERROR_SEVERITY()) + ', State ' + CONVERT(nvarchar(6),ERROR_STATE()) + ', Line ' + CONVERT(nvarchar(6),ERROR_LINE()) + NCHAR(13) + NCHAR(10) +
ERROR_MESSAGE();
PRINT @Error;
SET @RC = ERROR_NUMBER();
END CATCH;
/* --- Completed @Command against database 'model'. --- */
/* --- Running @Command against database 'msdb'. --- */
BEGIN TRY
INSERT INTO #ObjectCount (DatabaseID, DatabaseName, Objects)
SELECT DB_ID(N'msdb'), N'msdb', COUNT(*)
FROM [msdb].sys.objects
END TRY
BEGIN CATCH
SET @Error = N'The following error occured during the batch, but has been skipped:' + NCHAR(13) + NCHAR(10) +
N'Msg ' + CONVERT(nvarchar(6),ERROR_NUMBER()) + ', Level ' + CONVERT(nvarchar(6),ERROR_SEVERITY()) + ', State ' + CONVERT(nvarchar(6),ERROR_STATE()) + ', Line ' + CONVERT(nvarchar(6),ERROR_LINE()) + NCHAR(13) + NCHAR(10) +
ERROR_MESSAGE();
PRINT @Error;
SET @RC = ERROR_NUMBER();
END CATCH;
/* --- Completed @Command against database 'msdb'. --- */
/* --- Running @Command against database 'Sandbox'. --- */
BEGIN TRY
INSERT INTO #ObjectCount (DatabaseID, DatabaseName, Objects)
SELECT DB_ID(N'Sandbox'), N'Sandbox', COUNT(*)
FROM [Sandbox].sys.objects
END TRY
BEGIN CATCH
SET @Error = N'The following error occured during the batch, but has been skipped:' + NCHAR(13) + NCHAR(10) +
N'Msg ' + CONVERT(nvarchar(6),ERROR_NUMBER()) + ', Level ' + CONVERT(nvarchar(6),ERROR_SEVERITY()) + ', State ' + CONVERT(nvarchar(6),ERROR_STATE()) + ', Line ' + CONVERT(nvarchar(6),ERROR_LINE()) + NCHAR(13) + NCHAR(10) +
ERROR_MESSAGE();
PRINT @Error;
SET @RC = ERROR_NUMBER();
END CATCH;
/* --- Completed @Command against database 'Sandbox'. --- */
/* --- Running @Command against database 'TestBed'. --- */
BEGIN TRY
INSERT INTO #ObjectCount (DatabaseID, DatabaseName, Objects)
SELECT DB_ID(N'TestBed'), N'TestBed', COUNT(*)
FROM [TestBed].sys.objects
END TRY
BEGIN CATCH
SET @Error = N'The following error occured during the batch, but has been skipped:' + NCHAR(13) + NCHAR(10) +
N'Msg ' + CONVERT(nvarchar(6),ERROR_NUMBER()) + ', Level ' + CONVERT(nvarchar(6),ERROR_SEVERITY()) + ', State ' + CONVERT(nvarchar(6),ERROR_STATE()) + ', Line ' + CONVERT(nvarchar(6),ERROR_LINE()) + NCHAR(13) + NCHAR(10) +
ERROR_MESSAGE();
PRINT @Error;
SET @RC = ERROR_NUMBER();
END CATCH;
/* --- Completed @Command against database 'TestBed'. --- */
/* --- Running @Command against database 'Silly[DB]Name'. --- */
BEGIN TRY
INSERT INTO #ObjectCount (DatabaseID, DatabaseName, Objects)
SELECT DB_ID(N'Silly[DB]Name'), N'Silly[DB]Name', COUNT(*)
FROM [Silly[DB]]Name].sys.objects
END TRY
BEGIN CATCH
SET @Error = N'The following error occured during the batch, but has been skipped:' + NCHAR(13) + NCHAR(10) +
N'Msg ' + CONVERT(nvarchar(6),ERROR_NUMBER()) + ', Level ' + CONVERT(nvarchar(6),ERROR_SEVERITY()) + ', State ' + CONVERT(nvarchar(6),ERROR_STATE()) + ', Line ' + CONVERT(nvarchar(6),ERROR_LINE()) + NCHAR(13) + NCHAR(10) +
ERROR_MESSAGE();
PRINT @Error;
SET @RC = ERROR_NUMBER();
END CATCH;
/* --- Completed @Command against database 'Silly[DB]Name'. --- */
/* --- Running @Command against database 'Mc''Donald''s_Farm'. --- */
BEGIN TRY
INSERT INTO #ObjectCount (DatabaseID, DatabaseName, Objects)
SELECT DB_ID(N'Mc''Donald''s_Farm'), N'Mc''Donald''s_Farm', COUNT(*)
FROM [Mc'Donald's_Farm].sys.objects
END TRY
BEGIN CATCH
SET @Error = N'The following error occured during the batch, but has been skipped:' + NCHAR(13) + NCHAR(10) +
N'Msg ' + CONVERT(nvarchar(6),ERROR_NUMBER()) + ', Level ' + CONVERT(nvarchar(6),ERROR_SEVERITY()) + ', State ' + CONVERT(nvarchar(6),ERROR_STATE()) + ', Line ' + CONVERT(nvarchar(6),ERROR_LINE()) + NCHAR(13) + NCHAR(10) +
ERROR_MESSAGE();
PRINT @Error;
SET @RC = ERROR_NUMBER();
END CATCH;
/* --- Completed @Command against database 'Mc''Donald''s_Farm'. --- */
/* --- Running @Command against database '123'. --- */
BEGIN TRY
INSERT INTO #ObjectCount (DatabaseID, DatabaseName, Objects)
SELECT DB_ID(N'123'), N'123', COUNT(*)
FROM [123].sys.objects
END TRY
BEGIN CATCH
SET @Error = N'The following error occured during the batch, but has been skipped:' + NCHAR(13) + NCHAR(10) +
N'Msg ' + CONVERT(nvarchar(6),ERROR_NUMBER()) + ', Level ' + CONVERT(nvarchar(6),ERROR_SEVERITY()) + ', State ' + CONVERT(nvarchar(6),ERROR_STATE()) + ', Line ' + CONVERT(nvarchar(6),ERROR_LINE()) + NCHAR(13) + NCHAR(10) +
ERROR_MESSAGE();
PRINT @Error;
SET @RC = ERROR_NUMBER();
END CATCH;
/* --- Completed @Command against database '123'. --- */
/* --- Running @Command against database '''I made a good decision'''. --- */
BEGIN TRY
INSERT INTO #ObjectCount (DatabaseID, DatabaseName, Objects)
SELECT DB_ID(N'''I made a good decision'''), N'''I made a good decision''', COUNT(*)
FROM ['I made a good decision'].sys.objects
END TRY
BEGIN CATCH
SET @Error = N'The following error occured during the batch, but has been skipped:' + NCHAR(13) + NCHAR(10) +
N'Msg ' + CONVERT(nvarchar(6),ERROR_NUMBER()) + ', Level ' + CONVERT(nvarchar(6),ERROR_SEVERITY()) + ', State ' + CONVERT(nvarchar(6),ERROR_STATE()) + ', Line ' + CONVERT(nvarchar(6),ERROR_LINE()) + NCHAR(13) + NCHAR(10) +
ERROR_MESSAGE();
PRINT @Error;
SET @RC = ERROR_NUMBER();
END CATCH;
/* --- Completed @Command against database '''I made a good decision'''. --- */
--Turning XACT_ABORT back on due to @Exit_On_Error parameter
SET XACT_ABORT ON;
/* --- Post Command Begin. --- */
USE master;
SELECT * FROM #ObjectCount;
/* --- Post Command End. --- */ 