Data Management

Take advantage of undocumented SQL Server iteration procedures

Here's a look at how you can use two built-in system stored procedures -- sp_msforeachdb and sp_msforeachtable -- to iterate through SQL Server tables and databases.
I rarely encourage the use of iteration when 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 that 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.

Next time

In my next article, I'll show you how to build your own custom iteration procedure.

Tim Chapman a SQL Server database administrator and consultant who works for a bank in Louisville, KY. Tim has more than eight years of IT experience, and he is a Microsoft certified Database Developer and Administrator. If you would like to contact Tim, please e-mail him at chapman.tim@gmail.com.

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

TechRepublic's Servers and Storage newsletter, delivered on Monday and Wednesday, offers tips that will help you manage and optimize your data center. Automatically sign up today!

About

Tim Chapman is a SQL Server MVP, a database architect, and an administrator who works as an independent consultant in Raleigh, NC, and has more than nine years of IT experience.

5 comments
mahrumer
mahrumer

Great article ... I can clearly say that there are quite a lot of advantages of being a Microsoft certified professional.

ArnoldZiffle
ArnoldZiffle

These system stored procedures are probably undocumented for a reason. They may go away or functionality may change. It's a bad idea to use undocumented functionality. I certainly would not use these code I'm writing for a client.

topinambour
topinambour

For your client you could create your own procedure with 'sp_msforeachtable' as model. To get the script : exec sp_helptext 'sp_msforeachtable'

chapman.tim
chapman.tim

Well, I certainly didn't mean for them to be used in production level code, so I do agree w/ you there. However, they are very very useful when doing ad-hoc type of work...and even though they are "undocumented"...they can be easily recreated for future versions of SQL Server if necessary...

setivi
setivi

Right-on chapman. Very useful and time saver snippets for ad-hoc type of work.

Editor's Picks