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.
- Iteration uses Loop which is not recommended because Loops are harder to manage in databases and are slower than SET-based constructs.
- 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.