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.
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.
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
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.
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 email@example.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!