Skip navigation

Reader to Reader: Undocumented Power

Using one-line cursor queries

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

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.

Hide comments


  • Allowed HTML tags: <em> <strong> <blockquote> <br> <p>

Plain text

  • No HTML tags allowed.
  • Web page addresses and e-mail addresses turn into links automatically.
  • Lines and paragraphs break automatically.