Common uses for sp_MSforeachdb  

Posted by ReelTym

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

  2. EXEC sp_MSforeachdb 'USE ?; PRINT DB_NAME()'

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

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

  5. 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:

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

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

  8. EXEC sp_MSforeachdb 'USE ?; EXEC sp_spaceused'

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

  10. EXEC sp_MSforeachdb 'USE ? EXEC sp_helpfile;'

  11. Change Owner of all databases to 'sa'

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

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

  14. 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.

This entry was posted on Wednesday, September 15, 2010 and is filed under , . You can leave a response and follow any responses to this entry through the Subscribe to: Post Comments (Atom) .