The system procedure, sp_MSforeachb, is often used when you want to run the same command against all databases. But it is no bueno. It is undocumented, unsupported, and has a known but unresolved bug where it can skip databases due to the type of cursor it uses. Microsoft won't acknowledge the bug because doing so would be admitting Fight Club exists.
In 2010, I wrote a replacement, sp_foreachdb, which has a lot more flexibility, and does not exhibit the "skip databases" bug. This procedure became a part of Brent Ozar's First Responder Kit in 2016, but was recently deprecated. It has a few issues of its own; for example, it can't be used as the target of INSERT ... EXEC, and it can't run commands like DB_NAME() in the context of the selected database.
The new replacement, sp_ineachdb, which I wrote about here and here, is preferred over the above options. It truly runs each command in the context of each database, is easily extensible, and has fewer obstacles for implementation into your own code (for example, it *can* be the target of INSERT ... EXEC).
You're still on your own in terms of support, though contributors have stepped up to make improvements. I'm not the only one, but here are two pull requests I've submitted:
- In January, after a ping from a colleague, I added a new argument,
@exclude_pattern. Zach wanted to be able to exclude all databases that matched a pattern (e.g. skip all databases with names that start withdba_%). This simple pull request – targeted for the February release – allows you to do just that, though if you wanted to exclude more than one pattern in the same call, you'd have to modify the procedure further (think@exclude_pattern1,@exclude_pattern2, and so on).
- Today I added logic (#2508) to fix an issue raised by Andy Mallon (#2507), where he suggested that distribution databases should be considered system.
Happy looping!
Roy, can you explain the problem? The only database names that should cause problems are ones that contain apostrophes. Have you tried it? What was the result?
Hi Aaron, I've got database names that look as follows:
dbo.Trek.Turner.Trio
dbo.Flit.FishPro.Flint
dbo.Client.Report
How can I modify the procedure you have given to fit to my
needs. Please give me a direction of what approach I can take.
Your time is much appreciated.
Thanks a lot
Roy
I'm trying to parameterize this approach. The user should be able to pass in a parameter, like the name of the database, and I run this routine using that data. The specific case where I want to use it is to create database snapshots. This particular DB has 30 filegroups and users shouldn't have to know all the internal details to create and use snapshots.
As a sample, this is the SQL I'm trying to parameterize:
EXECUTE sp_MSForEachDB
'N("IF "?" = "master"
BEGIN
USE ?;
SELECT DB_NAME() from sys.tables;
END"); '
My attempt looks like this:
DECLARE @db AS NVARCHAR(100);
DECLARE @SQLQuery AS NVARCHAR(100);
DECLARE @ParameterDefinition AS NVARCHAR(100);
/* set the parameter value */
SET @db = 'FitnesseTest';
/* Build Transact-SQL String by including the parameter */
SET @SQLQuery = 'EXECUTE
N"IF "?" = "@db1"
BEGIN
USE ?;
SELECT DB_NAME() from sys.tables;
END";'
/* Specify Parameter Format */
SET @ParameterDefinition = '@db1 NVARCHAR(100)';
/* Execute Transact-SQL String */
EXECUTE sp_MSForEachDB @SQLQuery, @ParameterDefinition, @db;
I've tried every combination of 2, 3, 4, and 5 quotes to properly escape the single inner quote and I just can't find the right combination.
I'm trying to parameterize this approach. The user should be able to pass in a parameter, like the name of the database, and I run this routine using that data. The specific case where I want to use it is to create database snapshots. This particular DB has 30 filegroups and users shouldn't have to know all the internal details to create and use snapshots.
As a sample, this is the SQL I'm trying to parameterize:
EXECUTE sp_MSForEachDB
'N("IF "?" = "master"
BEGIN
USE ?;
SELECT DB_NAME() from sys.tables;
END"); '
My attempt looks like this:
DECLARE @db AS NVARCHAR(100);
DECLARE @SQLQuery AS NVARCHAR(100);
DECLARE @ParameterDefinition AS NVARCHAR(100);
/* set the parameter value */
SET @db = 'FitnesseTest';
/* Build Transact-SQL String by including the parameter */
SET @SQLQuery = 'EXECUTE
N"IF "?" = "@db1"
BEGIN
USE ?;
SELECT DB_NAME() from sys.tables;
END";'
/* Specify Parameter Format */
SET @ParameterDefinition = '@db1 NVARCHAR(100)';
/* Execute Transact-SQL String */
EXECUTE sp_MSForEachDB @SQLQuery, @ParameterDefinition, @db;
I've tried every combination of 2, 3, 4, and 5 quotes to properly escape the single quote and I just can't find the right combination.
@tobi no, global in this sense does not quite mean the same thing as global temp tables, it is "global" to the connection, not to the instance. Local means that it is only in the current scope. So if a stored procedure (a) calls another stored procedure (b) that has a cursor, if that cursor is local, only b can see it, but if it is global, a can see it too. But only within the same connection – if two concurrent sessions are running the same code, they will each have their own copy of the cursor, whether it is local or global. The naming choice was poor and ambiguous IMHO – should have been something like MODULE_SCOPE or SESSION_SCOPE.
The problem, I believe, is that the default is susceptible to skipping any rows that are "touched" during execution, which means any status change to a database (even if it doesn't actually change a value, obtaining a lock on a row could affect this) – most things don't change frequently, like whether the db is trustworthy, but other things do depending on manual changes like maintenance or bulk operations (recovery model), code testing (compat level), and changes that require single_user. And then of course other things that change frequently on their own, like log_reuse_wait_desc.
And while that is just a theory, I have never been able to reproduce the problem with my own cursor overriding the defaults, even on the same system under the same workload when the skipping was observed frequently with the system procedure.
The source code shows a global cursor. Does this not mean that concurrent executions are trampling on each others database list? This might cause missed databases as well as duplicates.
@John: Try this modified version of sp_foreachdb that does not use INSERT/EXEC
https://gist.github.com/wqweto/7d87441280e57a948807
Your SP uses an EXEC within it so when I try to insert the results into a table I get the error "An INSERT Exec statement cannot be nested." Is there any way around this while using your foreach proc?
Example:
DECLARE @dbName Varchar(100), @SQL NVARCHAR(1000)
DECLARE @DBInfo TABLE
(DatabaseName VARCHAR(100));
SELECT @SQL = 'USE ?; SELECT "?" AS DatabaseName'
INSERT INTO @DBInfo
(DatabaseName)
EXEC usp_dba_foreachdb @SQL
SELECT * FROM @DBInfo
I just ran into this same issue and through further testing it appears that the databases I was missing were those that I had no permissions to. I had permissions into most all of the databases on the server and didn't notice that the ones missing might actually be related. Aaron I've started using code with a few minor changes, so I don't have to wonder what's happening under the hood anymore. Great post.
Runa, do you have a database name with a length approaching 128 characters? Otherwise, I can't really troubleshoot your code if I can't *see* your code.
Hello, I recently started to use this SP.
However, i am seeing the error message thrown out.
Maximum length is 128
I quoted with single.. i am nor sure why is it showing the error.
thanks
Has anyone seen sp_MSforeachdb consistently miss a database? We have seen some of our monitoring code (using other methods) miss a database but if come back for a second try right away finds it, like the database status is fleetingly inaccessable.
Thank you very much for this.
We have an Agent job that must run hourly against multiple databases to support an application. It had been fine without any (known) issues for well over two years, but in the last two weeks it has randomly failed to process several databases each day. I thought that I was going crazy until I read your post and realized that other people were experimenting the same issues.
This should be fixed or dropped. It is a potential issue waiting to happen. I've since switched over to your SP and everything seems fine again so far.
Andrew Jessop, you're my hero.
I change database "master" to one database current and i works.
I too have had the problem with sp_msforeachdb where it just stops early, without processing all of the databases
I use the command to retrieve the results from sys.dm_db_index_physical_stats and store the results in my own database to analyse the index fragmentation in each database, but when run on the SQL server with the largest number of databases it occasionally cuts out and just stops after 30 or 40 databases, after 10 minutes or so. No error message.
I used Aarons tips to write my own version (very much cut-down):
BEGIN
SET NOCOUNT ON;
DECLARE @dbname nvarchar(100);
DECLARE @sqlcmd nvarchar(1000);
DECLARE DBs CURSOR STATIC FOR
/* define HERE the list of databases to be processed */
SELECT name FROM sys.databases WHERE name NOT IN ('TempDB','Model') ORDER By name;
OPEN DBs;
FETCH NEXT FROM DBs INTO @dbname;
WHILE @@FETCH_STATUS = 0
BEGIN
/* Write a procedure to do the work on each database */
SET @sqlcmd = 'DBadministration.dbo.dba_fragmentation ' + QUOTENAME(@dbname);
PRINT @sqlcmd;
EXECUTE sp_executesql @sqlcmd;
FETCH NEXT FROM DBs INTO @dbname;
PRINT 'Next: ' + CONVERT(NVARCHAR(20),GETDATE(),120) + ', ' + @dbname + ', Fetch Status ' + CAST(@@FETCH_STATUS as NVARCHAR(4))
PRINT '—————————————————————–';
END
CLOSE DBs;
DEALLOCATE DBs;
PRINT '=================================================================';
END
dba_fragmentation is my stored procedure to process the results of sys.dm_db_index_physical_stats
The PRINT commands are diagnostics so I can see what's happening
The STATIC in DECLARE DBs CURSOR seems to be the essential part
Without it the process is prone to fail in the same way that sp_msforeachDB does, and when it fails it returns @@FETCH_STATUS = -2 on the last database.
That means that the record it is trying to retrieve next has been deleted from the record set. As the databases are clearly not being deleted, I am wondering if any change to the sys.databases record is reflected in the results set by an apparent deletion. There are a number of dynamic fields in sys.databases such as log_reuse_wait, and is it possible that a change to one of these values during the course of the procedure would make the sys.databases record 'disappear'?
Anyway, putting STATIC in (as it is in Aaron's script) puts the databases list into tempdb and then retrieves it from there where it is not changed by anything that happens in sys.databases
Lo and behold, when I put PRINT @@FETCH_STATUS at the end of the original procedure running sp_msforeachDB, it returns -1 (end of set) when it does work and -2 when it does not. Looks like sp_msforeachdb doesn't have a STATIC in its cursor
Sorry, I should have replaced [Administration} with a generic [database_name] in the previous post.
Aaron, no I don't (although some are pretty long – not my doing!), but when executing the query it gave this error for each database:
Msg 103, Level 15, State 4, Line 3
The identifier that starts with '[Administration].sys.procedures AS p
INNER JOIN [[Administration].sys.sql_modules AS m
ON p.[obj' is too long. Maximum length is 128.
Msg 102, Level 15, State 1, Line 7
Incorrect syntax near '='.
Have you tried running the query yourself?
Anyway, you pointed me in the right direction with sys.sql_modules so thanks very much for that.
Liam, I'm not sure I understand. Do you have procedure names that are longer than 128 characters? Perhaps you should be using nvarchar for your column data types instead of varchar?
Not sure why the formatting didn't work on my previous post – it looked fine in SSMS!
Thanks Aaron. I found that I came up against the 128 character limit for the sys.procedures.name column when trying this so what I ended up doing was this:
CREATE TABLE #procs([db_name] varchar(50), proc_name varchar(255), [object_id] int )
EXEC dbo.sp_foreachdb
@command = N'INSERT #procs SELECT N"?", name, [object_id] FROM ?.sys.procedures'
CREATE TABLE #modules([db_name] varchar(50), definition nvarchar(max), [object_id] int )
EXEC dbo.sp_foreachdb
@command = N'INSERT #modules SELECT N"?", definition, [object_id] FROM ?.sys.sql_modules'
SELECT p.[db_name], p.proc_name FROM #procs p
INNER JOIN #modules m
ON p.[db_name] = m.[db_name] AND p.[object_id] = m.[object_id]
WHERE m.definition LIKE '%EXEC (%';
DROP TABLE #procs;
DROP TABLE #modules;
Hi Liam, I have two potential explanations.
1) Is it possible some of your "EXEC (" syntax actually has "EXEC(" or "EXEC (" where the space is actually a tab or more than one space?
2) Also, can you please ensure that you use ?.sys.sql_modules, since OBJECT_DEFINITION(object_id) will run in the calling database, not the target – meaning you will only reliably capture procedures in the database where you run the command. Your query should be:
EXEC dbo.sp_foreachdb
@command = N'INSERT #results SELECT p.name, N"?"
FROM [?].sys.procedures AS p
INNER JOIN [?].sys.sql_modules AS m
ON p.[object_id] = m.[object_id]
WHERE m.definition LIKE "%EXEC (%";';
I've tried using your stored procedure to search for stored procedures that may be open to SQL Injection by using the statement shown below, but it seems to be a bit hit and miss in that it will report stored procs that do contain EXEC (, but also many that don't. What am I doing wrong?
EXEC dbo.sp_foreachdb
@command = N'INSERT #results SELECT name, N"?"
FROM ?.sys.procedures
WHERE OBJECT_DEFINITION(object_id) LIKE "%EXEC (%";'
I remember reading this at the New Year and thinking, "That's kinda cool but I haven't had any problems with sp_msforeachdb and I have bigger fish to fry." Then this morning happened. Thanks for posting a solution to my problem 3 months before I needed it!
Very nice approach.. it's very useful. But do you remember from your conversation with the guys on Twitter, the cases you guys were unable to list some of the databases? Because by looking internally at the code from sp_MSforeachdb and sp_MSforeach_worker, you can find "lots" of IF's and WHERE clauses that could limit the rows (databases) returned by the original sp. Your version is more "transparent". MS could definitely improve their version. Cheers!
I've rolled my own before. I use UNION ALL to combine the results into a single resultset.
Nicely done though – I didn't do all the stored procedures for the different options.
Nope, just know that I can't rely on it (and maybe it explains why it's undocumented / unsupported – given how simple the basic approach needs to be, it shouldn't be this way).
I've also seen sp_MSforeachdb miss databases — did you ever figure out why that happens?