Iterating through tables and databases in SQL Server

This tip presents two very useful stored procedures that live in the master database but are not mentioned in SQL Server Books Online. These system procedures come in very handy for jobs such as determining the space used, the number of rows, the indexes on the user tables, and so on.

The first procedure, sp_MSForEachDB, runs up to three commands against every database on the server of interest.

|> @command1: first command to execute
|> @replacechar: substitute another placeholder for the "?"
|> @command2: second command to execute
|> @command3: third command to execute
|> @precommand: execute command once before entering the loop
|> @postcommand: execute command once after exiting the loop

Each set of commands (even if the set consists of only one command) is run as a batch against the databases, so it's most useful to capture the output to text rather than to the standard result-set grid. To do this, select Query | Results To | Results To Text from the menu or press [Ctrl]T.

The following code returns the number of user tables in each database on the server:

exec sp_MSForEachDB
@command1 = "use ? exec sp_SpaceUsed"

The abbreviated output looks like this:

database_name database_size unallocated space
--------------------- ------------------ ------------------
master 5.25 MB 1.26 MB

reserved data index_size unused
------------------ ------------------ ------------------ ------------------
2808 KB 1144 KB 1080 KB 584 KB

The second procedure, sp_MSForEachTable, accepts seven parameters:

|> @command1: first command to execute
|> @replacechar: substitute another placeholder for the "?"
|> @command2: second command to execute
|> @command3: third command to execute
|> @whereand: Where condition (or Order By clause)
|> @precommand: execute command once before entering the loop
|> @postcommand: execute command once after exiting the loop

By naming the parameters you want to pass, you can skip the necessity of passing null values. The @replacechar parameter is useful when the commands you want to execute contain a question mark. The @whereand parameter was implemented so you can scope the output according to a filter.

You can also sneak in an ORDER BY clause. The following example returns the row counts from every table in the AdventureWorks database, and sorts them by table name:

exec sp_MSForEachTable
@command1 = "Print \'?\'"
, @command2 = "select count(*) from ?"
, @whereand = "ORDER BY 1"

Here are a few rows of the output:

[HumanResources].[Department]

-----------
296

I like that it sorts the tables by both schema and table name.