Sometimes you need to run a query through all your databases. Depending the server you can do it easily if you don’t have a lot of dbs. But in other cases it can take you a lot of time. So why not querying them all ?
For that purpose we will use the stored procedure sp_MSforeachdb. This stored procedure take a query as a parameter.
One particularity to not forget is that you have to put USE ? before your query to tell that you want to change the current DB. The interrogation mark will take the value of the current DB.
Example: EXEC sp_MSforeachdb 'USE ? SELECT DB_NAME() AS DatabaseName'
With that query, it will loop through ALL the databases. Even the system ones.
If you want to be a bit more secure you can choose to exclude the system databases by checking the current database before executing your query.
Example with the previous query :
EXEC sp_MSforeachdb 'IF ''?'' NOT IN(''master'', ''model'', ''msdb'', ''tempdb'') BEGIN USE ? SELECT DB_NAME() as DatabaseName END'
A cleaner way is also to put your request into a variable :
DECLARE @Query VARCHAR(500) = 'IF ''?'' NOT IN(''master'', ''model'', ''msdb'', ''tempdb'') BEGIN USE ? SELECT DB_NAME() as DatabaseName END'
EXEC sp_MSforeachdb @QueryWith that sort of query you will probably gain time … Or at least spend less 😉
