Almost every DBA or anyone who has got a good exposure of working with SQL must have used this procedure i.e. sp_msforeachdb. This is a system level stored procedure as indicated by its nomenclature too being prefixed with “sp_“. I call it a hidden gem because this is not documented in Microsoft’s official documentation and that’s why, at various places you may find few disclaimers saying that use it on your own risk but I have been very much comfortable with this or say at least so far.
Those who have not got a deal with this, let us explore what it is, how to execute this and its possible use cases –
This is used to execute a command/query in each database. If you have an administrative task that you want to execute in each database on the underlying instance OR want to pull some details from each database, you can simply execute your query through this stored procedure. It has a basic syntax as –
EXEC sys.sp_MSforeachdb 'SELECT * FROM sys.tables'
See, very simple…you just pass the query as a parameter to this SP. You may wrap your query in a variable too as –
DECLARE @cmd NVARCHAR(MAX) = N'SELECT * FROM sys.tables'
EXEC sys.sp_MSforeachdb @cmd
If you execute this query, it gets executed for each database. Simple, isn’t it? Yeah….Wait…Wait…Wait. Let me show you the output of this query –
Anything to be noticed? It is showing the same output against each database execution.
Yes, this is true that it gets executed for each database but in order to execute the query in each database’s scope, you need to mention it as part of the query itself by referring the database using “?” as below, otherwise, it gives you the same result by executing it within the context of current database though still executing it for the number of databases on the instance –
DECLARE @cmd NVARCHAR(MAX) = N'USE ? SELECT * FROM sys.tables'
EXEC sys.sp_MSforeachdb @cmd
Now, see the result – it gives you the expected output i.e. tables from each different database –
It’s better to enclose “?” in brackets so that if any of the database names has any special character, it doesn’t fail. Also, we can verify that each execution happened in a different database context by selecting the database name as shown in below. We are selecting the database name twice, one by “?” itself and secondly by using DB_NAME() function to double check that it’s not an illusion –
DECLARE @cmd NVARCHAR(MAX) = N'USE [?] SELECT ''?'' AS DatabaseName, DB_NAME(), * FROM sys.tables'
EXEC sys.sp_MSforeachdb @cmd
…and you can see that it got executed in all different databases –
You may further extend this as per your needs or use case by putting more conditions like not to execute the query for a particular database or against system databases.
You can give it a try to run the same query that we shared in previous post to check the row count of each table to get the row count of each table in each database.
It may sound simple but it really comes as a rescue when we need to deal with many databases for the same query execution and we don’t want to repeat the same task for each database manually.