Undocumented SQL Server iteration procedures

I rarely encourage the use of iteration when doing database programming because looping constructs in the database world tend to be harder to manage and much slower than set-based constructs. However, there are times when using iteration inside the database engine is useful.

I'll present a few examples using two undocumented system stored procedures provided by Microsoft. (Note: the examples in this article work in SQL Server 2000 and SQL Server 2005.)

Sp_msforeachdb and sp_msforeachtable allow you to pass TSQL statements that will be executed in a FOR...EACH loop fashion for each database on the instance or for each table in a given database. These procedures are very handy when you're performing sets of operations for everything in your system, such as backing up all user databases or capturing the sizes of each individual table.

sp_msforeachdb

The sp_msforeachdb system stored procedure accepts a TSQL string to be executed against each database that resides on your SQL Server instance. This procedure is especially useful when you're performing database administration and maintenance tasks, such as backup operations. This example loops through each database on the server and prints out the database name:

EXECUTE sp_msforeachdb 'USE ? PRINT DB_NAME()'

The code snippet may be a bit confusing if you haven't seen this in practice. Notice the use of the question mark (?); this character represents the name of the database returned at each iteration of the internal loop. I can use the question mark anywhere in the script where I would normally use the name of the database.

With just a bit of tweaking, I can change the above code into a statement that will create a full backup of all the user databases on the current server instance. For example:

EXECUTE sp_msforeachdb 'USE ?
IF DB_NAME() NOT IN(''master'',''msdb'',''tempdb'',''model'')
BACKUP DATABASE ? TO DISK = ''G:?.bak, WITH INIT'''

Notice the use of double tick (') marks; these marks are used frequently in dynamically built TSQL code and represent a single tick mark. Single tick marks are commonly used to mark the beginning or ending of string literal statements.

sp_msforeachtable

The sp_msforeachtable system stored procedure is very similar to the sp_msforeachdb procedure except that it loops through all of the tables contained in the current database. This procedure is great for operations such as gathering statistics and bulk operations on sets of tables. In the following example, I use sp_msforeachtable to invoke the stored procedure sp_spaceused and pass the table name.

CREATE TABLE #TableSizes
(
TableName NVARCHAR(255),
TableRows INT,
ReservedSpaceKB VARCHAR(20),
DataSpaceKB VARCHAR(20),
IndexSizeKB VARCHAR(20),
UnusedSpaceKB VARCHAR(20)
)

INSERT INTO #TableSizes
EXEC sp_msforeachtable 'sp_spaceused ''?'''

SELECT * FROM #TableSizes
ORDER BY TableRows DESC

One of the most useful aspects of the code is that it inserts the results from sp_spaceused into a table. Note that I am not calling the sp_spaceused procedure itself; I'm calling it dynamically inside of the loop. Even through this method of code execution, I am able to capture the results and store them in a table for later use.