6 Common Uses of the undocumented Stored Procedure sp_MSforeachdb

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:

Print all the database names in a SQL Server Instance


EXEC sp_MSforeachdb 'USE ?; PRINT DB_NAME()'




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'




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'')'




Display the size of all databases in a SQL Server instance


EXEC sp_MSforeachdb 'USE ?; EXEC sp_spaceused'




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;'




Change Owner of all databases to 'sa'


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




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.


About The Author

Suprotim Agarwal, MCSD, MCAD, MCDBA, MCSE, is the founder of DotNetCurry, DNC Magazine for Developers, SQLServerCurry and DevCurry. He has also authored a couple of books 51 Recipes using jQuery with ASP.NET Controls and a new one recently at The Absolutely Awesome jQuery CookBook.

Suprotim has received the prestigous Microsoft MVP award for nine times in a row now. In a professional capacity, he is the CEO of A2Z Knowledge Visuals Pvt Ltd, a digital group that represents premium web sites and digital publications comprising of Professional web, windows, mobile and cloud developers, technical managers, and architects.

Get in touch with him on Twitter @suprotimagarwal, LinkedIn or befriend him on Facebook

5 comments:

Brad Bowman said...

We have used the following in the past to script out our SQL 2000 databases and it worked well. I don't remember if I downloaded it or wrote it, so I do not want to take created for it's creation.
--------
Create the following SP, change the server to the server name:
Also change the location where the scripts are created and create the directory.

create proc sp_ScriptDatabase @dbname sysname
as
declare @command varchar(1000)
declare @texttime varchar(10)
set @texttime = convert(varchar, getdate(), 102)
set @command = '"C:\Program Files\Microsoft SQL Server\MSSQL\Upgrade\scptxfr.exe" /s Servername /I /d '
+ @dbname + ' /f c:\sqlscripts\' + @dbname + '_' + @texttime + '.txt /r'
print @command
exec master..xp_cmdshell @command



Schedule this to run as a job

exec sp_MSForeachDB "exec sp_ScriptDatabase ?"

Enjoy,
Brad

Suprotim Agarwal said...

Thanks Brad for that script. I have been using the Database Publishing Wizard till date (it can be run from a command line and thus can be automated)

Here are some interesting options:
http://stackoverflow.com/questions/181909/tool-for-scripting-table-data#181942

http://devio.wordpress.com/
2008/09/05/introducing-smoscript/

چندگانه said...

EXEC sp_MSforeachdb 'select "?" AS db, OBJECT_NAME(object_Id) FROM [?].sys.tables'

Found it here:
http://blog.sqlauthority.com/2009/04/26/sql-server-list-all-the-tables-for-all-databases-using-system-tables/

چندگانه said...

There is another usefull article, about the other unducoumented sp
sp_MSforeachtable

http://www.databasejournal.com/features/mssql/article.php/3441031/SQL-Server-Undocumented-Stored-Procedures-spMSforeachtable-and-spMSforeachdb.htm

Akhil Gupta said...

Visual Studio and SQL Server management Studio are two different entities. So it takes a lot of time when we try an interaction with the database from Visual Studio then it takes a lot of time.

When we are using desktop applications then we do not realize this time delay, but while using web applications the time delay becomes an important factor because the database is on the remote server so a huge time delay is observed.

Normally, the query that we write in Visual Studio is first compiled and then executed in Sql Server which then returns the results. So, this is why a time delay is observed.

The answer to this is Stored Procedure usage. The Stored Procedure is stored in compiled format in SQL server so when we call it from Visual Studio application then it is just executed. Thus saving a lot of time.

An illustration of proper usage of STORED PROCEDURE has been displayed in the following video. You may see it:

http://www.visiontechno.net/studymats/storeprocedure.html