A better sp_MSForEachDB
Though undocumented and unsupported, I’m sure that at least once you happened to use Microsoft’s built-in stored procedure to execute a statement against all databases. Let’s face it: it comes handy very often, especially for maintenance tasks.
The main difference with his (and Microsoft’s) implementation is the absence of a cursor. While flagged correctly (LOCAL FORWARD_ONLY STATIC READ_ONLY) and run against a temporary table, nevertheless I was a bit disturbed by that tiny little cursor, so I decided to get rid of it.
Basically, my code relies on a dynamic SQL pushed down three levels:
This trick can be used as many times as you like, given that you keep on declaring and passing all the parameters you need to the lower levels.
I didn’t provide ad-hoc parameters to implement complex filters on sysdatabases, as I’m convinced that they would not be useful enough in a day to day use. If you like this code and want to use it, feel free to change it to incorporate any kind of filter.
Here is the code:
Let’s see some examples of its use:
Print the database name for each user database:
EXEC [dba_ForEachDB] @statement = 'PRINT DB_NAME()', @replacechar = '?', @name_pattern = '[USER]'
Display the file path of each database file of system databases:
EXEC [dba_ForEachDB] @statement = 'SELECT physical_name, size FROM sys.database_files', @replacechar = '?', @name_pattern = '[SYSTEM]'
I hope you like it and find it useful.