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

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

-----------------------------------------

LinkedIn:

in.linkedin.com/in/rohitpkhare

Twitter:

@BuzzRohit

-----------------------------------------

Month List

Protected by Copyscape Web Plagiarism Software