Use sp_ineachdb Instead of sp_MSforeachdb

If you follow Brent Ozar’s blog, you may have noticed a post a few weeks ago about making September Free Community Tools Awareness Month. I love the statement that “Your first reaction is gonna be that everybody already knows it, but trust me, they do not.” I’m not sure if this post will fall under that category but there’s only one way to find out as we explore sp_ineachdb.

Hidden Treasure

If you’re reading this post then you’re probably familiar with the SQL Server First Responder Kit. If not, go check it out and come back.

The SQL Server First Responder kit features procedures like sp_blitz, sp_BlitzCache, and others that make life as a DBA easier. But you might not know there is a procedure included that is not of the sp_Blitz variety which can be just as useful.

That procedure is sp_ineachdb.

You may be thinking to yourself “Hey, I think I’ve heard of that! Isn’t that unreliable and unsupported?”

Nope, you’re thinking of sp_MSforeachdb which is indeed unreliable and unsupported. I’d recommend this Stack Overflow post as an example of why that is.

The procedure sp_ineachdb was developed by Aaron Bertrand as an alternative to sp_MSforeachdb.

Around the World

Let’s look at an example of how sp_ineachdb can be used to query each database on a SQL Server.

My test environment has a wide range of databases. What if we want to check table counts in each one? Using sp_ineachdb, we can run the following:

EXEC dbo.sp_ineachdb @command = 
N'SELECT db_name() AS ''dbname'', COUNT(*) AS ''table_count'' FROM sys.tables;';

In seconds, we can run a query to find our counts and scroll through the results.

In some cases, this is all you need. In other cases, you may want all of the results in one result set so you can filter and order the data. That’s possible as well:

CREATE TABLE #Table_Counts (
	dbname SYSNAME
	,table_count INT
	);

INSERT #Table_Counts
EXEC dbo.sp_ineachdb N'SELECT db_name() AS ''dbname'', COUNT(*) AS ''table_count'' FROM sys.tables;';

SELECT dbname
	,table_count
FROM #Table_Counts
WHERE table_count >= 10
ORDER BY table_count DESC;
GO

DROP TABLE #Table_Counts;

We have our results in one table that we can examine ourselves or send off to whoever needs it.

Let’s look at one more example. What if the Page_Verify option needs set to CHECKSUM for specific databases? We could run:

EXEC sp_ineachdb
@command = N'ALTER DATABASE ? SET PAGE_VERIFY CHECKSUM WITH NO_WAIT;',
@database_list = 'StackOverflow2013, ExampleDB, AdventureWorks2019';

Touch ’em All

When the time comes to query each database on a server, don’t spend time running the same query database by database. Write it once and let sp_ineachdb do the heavy lifting.

Thanks for reading!