Data Management

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]

-----------

16

[HumanResources].[Employee]

-----------

290

[HumanResources].[EmployeeAddress]

-----------

290

[HumanResources].[EmployeeDepartmentHistory]

-----------

296

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

Check out the SQL Server archive, and catch up on the most recent editions of Arthur Fuller's column.

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

Get SQL tips in your inbox

TechRepublic's free SQL Server newsletter, delivered each Tuesday, contains hands-on tips that will help you become more adept with this powerful relational database management system. Automatically subscribe today!

5 comments
Realvdude
Realvdude

Wonderful! I hodged together a routine that uses a cursor on the sysdatabases and exec, but this is much cleaner.

manoj
manoj

I could not see the output as shown, for the second stored procedure. I mean, I was not able to see the table name just as they are shown in the example. Instead, it showed me "No column name".

Kannan.S
Kannan.S

I guess your current execute mode is set to "Results in Grid". To see the output as shown in the article, make sure you have set the execute mode to "Results in Text". To set this mode, use the "Query" menu of SQL Query Analyzer and choose the "Results in Text" option. kannan

steve.ingle
steve.ingle

Make sure you "USE" a database, prior to running the 2nd procedure

djj55
djj55

I ran without the output going to a file an received the same result. When I sent the output to a file it worked fine. djj