Editor's Note: Share your SQL Server discoveries, comments, problems, and solutions with other readers. Email your Reader to Reader contributions (400 words or less) to [email protected]. Please include your phone number. We edit submissions for style, grammar, and length. If we print your submission, you'll get $100.00
You often need to query the master database for all tables or databases and perform an action on the results, such as DBCC CHECKDB. Traditionally, you used a cursor to perform this type of query, as Listing 1 shows. But a query this size leaves a lot of room for error. In SQL Server 6.5 and 7.0, Microsoft ships a set of undocumented stored procedures that let you perform the query in one line.
Related: Locking and DBCC CHECKDB
Two little-known stored procedures, sp_ MSforeachtable and sp_MSforeachdb, ship with SQL Server 6.5 and 7.0 and let you substitute a question mark for a table or database name. You can run DBCC CHECKTABLE on each table in the database you're in by using the one-line cursor query
sp_MSforeachtable "print '?' DBCC CHECKTABLE ('?')"
Microsoft has improved sp_MSforeachdb in SQL Server 7.0. In SQL Server 6.5, you have to create a temporary table, populate the table, then run sp_ MSforeachdb, as Listing 2 shows. However, in SQL Server 7.0, you can run the following one-line query to perform a DBCC CHECKDB on all databases.
Sp_MSforeachdb @command1="print '?' DBCC CHECKTABLE ('?')"
Microsoft created the undocumented sp_MS stored procedures to use as GUI management tools. Microsoft increased the number of sp_MS procedures from 30 in SQL Server 6.5 to more than 280 in 7.0. Most new procedures in SQL Server 7.0 are for replication. To obtain a full list of sp_MS stored procedures, run the query
SELECT * FROM master..sysobjects WHERE name like 'sp_MS%'
If you want to modify an sp_MS stored procedure, make sure you save the new stored procedure under a different name to retain the original. Microsoft doesn't support editing sp_MS stored procedures, and by overwriting an existing one, you could interfere with the operation of SQL Server.
Another little-known stored procedure is sp_MStablespace. It uses the @name parameter and returns the number of rows and the space the table and index use. This stored procedure's cousin, sp_MSindexspace, uses the @tablename parameter. For a specified table, this procedure returns all the indexes and reports the size of the indexes.
Sp_MShelpcolumns with the @tablename parameter shows the complete schema for a table, including the length, type, name, and whether a column is computed. After you calculate a column (average profit, for example), sp_ MShelpcolumns shows you how it was computed. If you're doing cascade deletes and want to know the names of the dependencies, sp_ MStablerefs with the @tablename parameter returns all the dependencies for any table. Combining all the sp_MS stored procedures makes a powerful tool, as the exercise in the sidebar shows.
