sp_MSforeachdb iterates through each database in a SQL Server instance. Instead of using a cursor, this Undocumented Stored Procedure is of immense help when I need to run a command against all the databases in my local server. Here are some scenarios where the sp_MSforeachdb can be practically used for your day to day tasks:

1. Print all the database names in a SQL Server Instance



EXEC sp_MSforeachdb 'USE ?; PRINT DB_NAME()'



2. Print all the tables in all the databases of a SQL Server Instance



EXEC sp_MSforeachdb 'USE ? SELECT DB_NAME() + ''.'' + OBJECT_NAME(object_Id) FROM sys.tables'



3. The example shown above prints the tables for master, model, msdb and tempdb. If you want to eliminate these databases in the query use, do the following:



EXEC sp_MSforeachdb '
    USE ?
    SELECT OBJECT_NAME(object_Id)
        FROM sys.tables
            where DB_NAME() NOT IN(''master'', ''model'', ''msdb'', ''tempdb'')
'



4. Display the size of all databases in a SQL Server instance



EXEC sp_MSforeachdb 'USE ?; EXEC sp_spaceused'



5. Determine all the physical names and attributes(size,growth,usage) of all databases in a SQL Server instance



EXEC sp_MSforeachdb 'USE ? EXEC sp_helpfile;'



6. Change Owner of all databases to 'sa'



EXEC sp_MSforeachdb 'USE ?; EXEC sp_changedbowner ''sa'''



7. Check the Logical and Physical integrity of all objects in the database



sp_MSforeachdb 'DBCC CHECKDB(?)' 


Similarly you can backup all databases at one go or do a CHECKSUM using this useful procedure. There are many more ways to use it. Got a useful tip on sp_MSforeachdb? Share it using the comments below.