Iterating through all the SQL Server databases and tables to execute any command.

by Rohit 25. April 2012 16:04
-

Sometimes you may want to execute a command against all the databases in an instance or all the tables in any database. SQL Server (2005 and above) contains two undocumented stored procedures, viz. sp_MSforeachdb and sp_MSforeacheable. To illustrate their use, I am taking a simple example to display all the databases and tables with the total space occupied.

What is the job of sp_MSforeachdb and sp_MSforeachtable?

Both the stored procedures are designed for iteration. With each iteration you can execute a command.

sp_MSforeachdb iterates through all the databases in the current instance, while sp_MSforeachtable iterates through all the tables in a database to perform a command.

You can view the details of each stored procedure by executing:

sp_help <name of system stored procedure>

from the query window. Below is the output of each:

 

Parameter Details
@command1 nvarchar(2000) field specifying the first command to run against each database. Can not be blank.
@replacechar nchar(1) field specifying the character in the command parameters to replace with the database name. Defaults: ?.
@command2 nvarchar(2000) field specifying the 2nd command to run against each database.
@command3 nvarchar(2000) field specifying the 3rd command to run against each database.
@precommand nvarchar(2000) field specifying a command to be run prior any commands against any database.
@postcommand nvarchar(2000) field specifying a command to be run after running all commands.

 

 

Script to display all databases with sizes in an instance using: sp_MSforeachdb

EXECUTE master.sys.sp_MSforeachdb 'USE [?]; EXEC sp_spaceused'

An alternate approach is by ignoring the system databases from the list. In that case:

EXECUTE sp_msforeachdb 'USE ?
IF DB_NAME() NOT IN(''master'',''msdb'',''tempdb'',''model'')
BACKUP DATABASE ? TO DISK = ''D:?.bak, WITH INIT'''

Script to display all the tables with total number of rows in a database using: sp_MSforeachtable

CREATE TABLE #t 
	( 
		Table_Name VARCHAR(80),
		Total_Rows CHAR(11),
		Reserved_Space VARCHAR(18), 
		Data_Size VARCHAR(18), 
		Index_Size VARCHAR(18),
		Unused_Space VARCHAR(18)
	) 

	INSERT #t EXEC sp_msForEachTable 'EXEC sp_spaceused ''?''' 

	SELECT * FROM #t ORDER BY Data_Size DESC

	DROP TABLE #t /* Drop Temp Table */

Note the ? used in all the code snippets above. It represents the name of the database or table returned at each iteration of the loop.

Disadvantages:

Both the stored procedures discussed above have a number of disadvantages. 

  1. Iteration uses Loop which is not recommended because Loops are harder to manage in databases and are slower than SET-based constructs.
  2. It is found that sp_MSforeachdb skips few databases from the list. Aaron Bertrand (Blog | twitter) writes in his blog about these issues. He also suggests a more reliable stored procedure to use instead of sp_MSforeachdb discussed above.

Tags: , ,

Database | Technology

Pingbacks and trackbacks (1)+

Add comment

biuquote
  • Comment
  • Preview
Loading

About Rohit Prakash

Software Craftsman @ Fortune 100 Company. Technical Reviewer of few programming books.

A Single, Writer, Reader and Adventurist.

You can reach me at:

rohit [at] irohitable.com

Month List

Protected by Copyscape Web Plagiarism Software