As a consulting database administrator (DBA), there is a long list of things I look at when first assessing the overall health of a client’s databases. A number of these checks require I issue the same query in every database. Some clients have a dozen or more SQL Server instances with any one containing 100 or more databases. Issuing all those queries and collecting the results could be a daunting task.
Although many people know Microsoft includes an undocumented stored procedure (sp_MSForEachDB) that looks like an ideal solution to this problem, there is a long list of drawbacks, not the least of which is that it is undocumented and therefore ill-advised to be used on production systems. Microsoft’s procedure uses a deprecated compatibility view in conjunction with a cursor to iterate over the databases and a temporary table in conjunction with a while loop to perform token replacement making performance pretty bad. Most ‘replacements’ one can find on the web suffer from the following issues:
- A separate result set returns for each database making sorting or use with the Registered Servers feature of SSMS harder.
- You cannot apply criteria to exclude databases such as read-only or offline ones.
- The replacement token is limited to a single character that limits query content and makes testing harder.
- The query itself is limited to 2000 characters AFTER token replacement.
- It can be difficult to troubleshoot query failures because there is no simple way to examine the ‘after replacement’ query text.
The example below, like sp_MSForEachDB, uses the string ‘?’ as the replacement token although with this implementation you can use almost any string you want. For example, I sometimes use ‘master’. This lets me test the query without the need to find and fix all the database references before I double up single quotes for stuffing into @src.
Notice my use of ‘…from [?].sys…’. I wrap ‘?’ with brackets because I want it to read ‘…from [db name].sys…’ after replacement in case a database name has a space in it. You might also notice this example shows how easy it is to resolve collation differences as results from different databases are merged together. The ‘collate database_default’ clause resolves to the default collation of the default database for your connection. Any iterating method that uses ‘Use <DBName>;…’ risks collation changes. You must then choose a specific collation to code into your query whereas the method below does not change the default database resulting in a constant database_default and insures values can not conflict.
As I said above, I am stuffing the query I want executed in every database into the varchar variable @src, similar to using sp_MSForEachDB, however I can now filter the list of databases (see where I specify: where state = 0 and is_read_only = 0) to exclude some. The “select @query = @query…” statement creates one large query using ‘union all’ between databases so a single result set automatically returns without any work and, as a side effect, if the number or data type of columns returned from one database (perhaps due to a different compatibility level) is different between any two databases, I will not get random failures interspersed with incomplete results. The query is either valid in all databases or execution will not occur. Another nice side effect is that the optimizer can decide to make this a parallel query and execute in multiple databases simultaneously to provide even faster results.
Since the generated query is often quite large and, personally I am not likely to execute it more than once, it does not make sense that the huge plan take up query plan cache space. For this reason I tack on ‘option(recompile);’ to prevent it being cached. Just prior to, or instead of, the option clause is a good place to add an ‘order by’ clause if you’d like the final result to be ordered.
If I want to see the generated statement rather than executing it, I can use ‘Select @query’ rather than ‘exec (@query)’. This makes troubleshooting syntax errors quite easy. The slightly more complex select command provided in the example (but commented out) takes advantage of an SSMS feature provided so you can view XML documents returned by a query in a separate window. The ‘trick’ being used is to imbed the generated query text in an XML processing instruction. This turns on the SSMS feature that makes the column contents clickable. You can then click the cell in the output window and SSMS opens a new document window containing only that cell’s content with line breaks, etc. intact for easy viewing. Note: You must be using ‘Results to grid’ for this to work. It is also remotely possible that you may need to change the [Options][Query Results][Results to Grid][Max Characters Retrieved][XML Data] limit to see the entire query.
There are many drawbacks to the undocumented procedure seemingly intended for use in the problem situation I have described, but you only need this snippet of code somewhere in your toolbox to have a faster and more flexible way to query multiple databases at once. If you use it often on the same instance, it is not hard to create a stored procedure to embody your particular logic. For example, the defaults of the procedure below mimic sp_MSForEachDB, but other defaults could mimic the example above. Your usage can explicitly ask for either, no matter how you set the defaults.