hile lurking on the SQL List server, I happened upon a suggestion that eliminates the need for writing your own cursors to accomplish common database administration tasks. These tasks apply to each database installed on a MSSQL (6.5) server. The tasks involved are things like running the DBCC (database consistency checker) routines to update index statistics, or to run a backup of a database.

The tip uses an undocumented system-stored procedure called ms_ForEachDB and its companion, ms_ForEachTable. I collected a few excellent examples of uses and applications for these procedures with respect to a few common DBA tasks. The examples were provided by the exchanges between Dave Canham, MCP, and Shaun Tinline, MCP, and they provided the ideas and guidance. Shaun was the original questioner, and he was kind enough to send a summation of the thread. We owe a great ‘thank you’ and debt of gratitude to them for giving their permission to share the exchanges and examples.

MSSQL Installation Databases
When MSSQL Server is installed, it installs several databases. They are named MSDB, Model, Temp, Master, and Pubs. MSDB stores alert and task information, Model stores database template information, Temp is a work area database used for sorts and formatting, and Pubs is the SQL Server example database. The Master database holds a server-wide system catalog and other system tables that store security and backup information, among other server statistics.

The Master Database also has a library of system-stored procedures installed with it. A stored procedure is a procedural program whose code is written in a database proprietary language. MSSQL Server’s name for its procedural language is T SQL, for example, and Oracle’s is named PL SQL.

Database Maintenance and SQL
All database activity (inserts, updates and deletes) is accomplished by Structured Query Language statements. The SQL 92 standard for Structured Query Language is probably the greatest point of convergence between products because all the vendors have to meet a minimum standard on their language implementation. The fact is that every vendor adds extensions to both their SQL implementation and their procedural language to highlight the strengths of their product.

Even though everything necessary to maintain the database is represented in the SQL language, there are times when a procedural code ‘wrapper’ around SQL statements adds a lot of convenience and repeatability to accomplishing the task at hand. Some of these tasks are dumping the database (backing up), loading the database (restoring), and granting access (administering security). A stored procedure is a procedural program that is stored, compiled, and executed in the database. It is written in a database-specific procedural language that acts as a wrapper around a Structured Query Language statement. We use the stored procedures provided by MSSQL Server to accomplish repetitive database administration tasks.

The DBA’s Helper
A DBA is responsible for maintaining the database’s availability, integrity, and security. To help in these tasks, the DBA can use the library of system-stored procedures to check on the status of the database, as well as run corrective procedures from the DBCC (database consistency check) routines. The system-stored procedures offer a routine called sp_MSForEachDB that helps the DBA by identifying the names of the databases on the server and executing an administrative SQL statement that applies to that database. Typically, a SQL Server is home to more than one database. First, you need to observe some caution in naming your own stored procedures.

Stored Procedure Naming
The Master database stored procedures are prefixed with an ‘sp__’. When you write your own stored procedures, it is recommended that you name them with a different prefix, instead of the ‘sp__’ convention used by the Master database’s stored procedures. This is because stored procedures prefixed with ‘sp__’ are executed in the context of whatever database you happen to be in when you invoke them. Consequently, there is a great likelihood of unintended results with a procedure named with ‘global’ applicability. Database professionals call this a ‘high whoops’ factor.

When you write your own stored procedures, you have two choices. Either let them accept a parameter for the database name in which to execute, or prefix them with something else that will signal them as user-created procedures so they won’t run by accident. (This touches on another very useful feature of stored procedures: that they accept parameters. Not only that, but they have parameters for input as you’d expect, and they can store parameters for output, too. But that’s another article.)

How about using ‘usp__’ and some other clue as to the purpose of the procedure for your own stored procedures? This little bit of caution could save you hours of recovery from unintended results caused by executing the right stored procedure in the wrong database.

Now that You Know What It Is, How Do You Use It?
As mentioned above, to maximize availability and integrity, the DBA has many checks to perform on the server. Using sp_MSForEachDB and its companion sp_MSForEachTable to execute these checks was not intuitive. When I tried to run them I got an error asking for a table that did not exist, in other words, sp_MSForEachDB required the existence of a temporary table called #SQLOLEDBUserProfile to execute. This type of requirement is not uncommon, and you can usually figure it out by opening up the code and reading it, but I didn’t figure this one out myself. I have to thank Dave for pointing this out.

--original msforeachdb exampleDROP TABLE #SQLOLEDBUserProfilego--In 6.5, you must create this temp table, #SQLOLEDBUserProfile, prior to using the Sp_MSForEachDB.--create temp tableCREATE TABLE #SQLOLEDBUserProfile(DBID int NOT NULL Primary Key, Profilebits int NOT NULL)--initializeEXEC sp_MSDBUserprofile 'init'  -----this must be run in the master database.---run taskEXEC sp_MSForEachDB 'USE ? SELECT DB_Name() EXEC sp_help '

This code will run the sp_help stored procedure in each database on the server.

Updating index statistics is still a DBA task in MSSQL 6.5. The update statistics command creates an up-to-date representation of the page that data is stored on, reconciling the data’s location to where the index says it is. In order to accomplish this, it is necessary to run the DBCC command update statistics against all user tables. The DBA also checks for the existence and constitution of indexes that relate the tables in the database. Instead of writing the cursor and getting involved in writing code, I learned that you could accomplish the same thing with one line at the ISQL window using the sp_MSForEachTable variant of the procedure.

EXEC sp_MSForEachTable  "update statistics ?"

Similarly, I found I could back up single tables with the line

EXEC sp_MSForEachTable "dump table ? to diskdump"

Checking current activity on the server showing locking and blocking can be neatly accomplished with a slight variation on Dave’s example.

EXEC sp_MSForEachDB 'USE ? SELECT DB_Name() EXEC sp_helplock'

Summary
The new features in SQL Server 7 go a long way to alleviating the monitoring tasks for the DBA. This is especially so in monitoring memory and storage details. The lion’s share of administrative and maintenance tasks that once occupied a significant effort in planning and tracking now take place under the hood. In MSSQL 6.5, however, these tasks remain the responsibility of the DBA. I hope these examples bring other uses to mind for you, so that you can use sp_MSForEachDB and sp_MSForEachTable to lighten your workload. I would like to express my sincerest thanks to the members of the SQL listserv at www.swynk.com for their help and willingness to share information.