foreach is for Everyone!

The procedure sp_MSforeachtable will repeat a set of commands against every table in the current database. You code the command as normal, but with a "?" character in place of the varying table name. The placeholder character "?" can be changed to something else if necessary (see the syntax definition in the sidebar "Syntax for sp_MSforeachtable"). As a simple example, here's an update of index statistics for all tables:

exec sp_MSforeachtable "UPDATE STATISTICS ?"

Table selection

The sp_MSforeachtable procedure works by establishing a cursor called hCForEach (more on this later) on the sysobjects system table in the current database. Procedure sp_MSforeachtable automatically includes a WHERE condition that restricts the type of object to user-defined tables. You can further restrict the scope of the procedure by adding additional AND clauses to select just those tables you want to process. This is done by including selection criteria in a parameter named @whereand (see the sidebar "Syntax for sp_MSforeachtable

"). When developing code using selection criteria, it's a good idea to test it by using the PRINT command to list the selected tables. As an example, the following code lists all tables with names that start with "pub":

exec sp_MSforeachtable "PRINT '?'", @whereand="AND name like 'pub%'"

The results, when run on the pubs database under SQL Server 7.0, are as follows. Note that the placeholder character is replaced by the table name qualified by its owner name. As you can see, version 7.0 automatically uses delimited identifiers with the new square brackets to ensure that any names with special characters are interpreted correctly. For the same reason, in version 6.5, you often need to put quotes around the placeholder character yourself.

[dbo].[pub_info] [dbo].[publishers]

In the previous example, selection was by table name. In fact, any AND clause that is valid on sysobjects will work (find a complete list of sysobjects columns in Books Online and in the T-SQL online Help). In practice, the most useful columns are name, uid (the user id of the table owner), crdate (date table created), and id (the table's object id). The user id uid can be used in conjunction with the USER_ID function. In the next example, all tables that belong to database user "Paul" are dropped. Note that I've included a PRINT statement in order to see a list of the tables that were successfully dropped (SQL Server doesn't issue a message confirming success of its DROPs):

exec sp_MSforeachtable "DROP TABLE ? PRINT '? dropped' ", @whereand="AND uid=USER_ID('Paul')"

I ran this code on a pubs database that had copies of some of the tables created under the user name `Paul.' The results were as follows:

[Paul].[pub_info] dropped [Paul].[publishers] dropped

Each database object is given a unique system-generated identifier -- its object id -- whose value is held in the id column in sysobjects. Many system tables include id as a foreign key, which allows you to define quite sophisticated table selection criteria with this object id, usually by using subselects to reference those other system tables. When you reference the id column in such a subselect, you have to specify it as o.id (you're qualifying it with the alias name that's used for sysobjects in the hCForEach cursor).

As an example, consider the sysindexes system table. This holds one row for each index over each table. (Again, refer to Books Online or the T-SQL online Help for more info on sysindexes.) There's also a summary row for each table (column indid = 0 or 1) that gives the number of rows in the table and the total space (excluding text/image datatypes) used by the table and all of its indexes. Hence, it's possible to select tables based either on row counts or space used. For instance, the next example shows how to carry out a DBCC check, but only on those tables with less than 100,000 rows:

exec sp_MSforeachtable "DBCC CHECKTABLE('?')", @whereand=" AND (select rows from sysindexes where sysindexes.id = o.id and indid < 2) < 100000"

Controlling table sequence

The value in the @whereand parameter is added to the end of the SELECT statement that defines the hCForEach cursor. This means that you can add any other SELECT clauses that follow the WHERE ... AND statements. I often include an `ORDER BY name' clause to ensure that tables are processed in alphabetic order (see the next example). This makes it easy to compare results between two different databases with similar structures.

pre and post commands

Procedure sp_MSforeachtable provides optional parameters that allow you to specify two further commands: @precommand is executed once before starting the table loop, and @postcommand is run once afterwards. The best use I've found for these is for printing messages, as can be seen in the following example. This one grants public read access to all tables in a database:

exec sp_MSforeachtable "PRINT '?' GRANT SELECT ON ? TO Paul", @whereand="ORDER BY uid, name", @precommand="PRINT 'Granting permissions on...'", @postcommand = "PRINT '... procedure complete.'"

When run against the pubs database, the results look like this:

Granting permissions on... [dbo].[authors] [dbo].[discounts] [dbo].[employee] [dbo].[jobs] [dbo].[pub_info] [dbo].[publishers] [dbo].[roysched] [dbo].[sales] [dbo].[stores] [dbo].[titleauthor] [dbo].[titles] ... procedure complete.

The programmer's comments in the source for sp_MSforeachtable suggest that the @precommand and @postcommand parameters can be used to employ a temporary table to capture and process the results from the repeated commands. For example, if procedure sp_spaceused is used as the command, it would produce a separate result set for each table. This is okay, but it would be more effective if the individual results could be combined into a single table.

The way to do this is to use a temporary table that's populated with repeated executions of an INSERT statement using sp_spaceused to get the data. Of course, the temporary table has to be created beforehand (presumably using @precommand) to contain all of the columns needed to hold the results of sp_spaceused. After all of the tables have been processed, you can then produce your final report from the temporary table in @postcommand. By the way, the command in @postcommand is executed only if the previous commands have all run successfully.

The drawback is that sp_MSforeachtable actually executes @precommand within a direct EXEC() statement, and any local temporary table created like this is unavailable outside of the EXEC(). A solution is to use global temporary tables (prefix the table name with ##). You can try this for yourself, but I feel that all of the extra parameters make the code look more confusing. I prefer to create and use the temporary table outside of the sp_MSforeachtable procedure, as follows:

CREATE TABLE #msforeach (name varchar(30), rows varchar(12), reserved varchar(18), data varchar(18), index_size varchar(18), unused varchar(18) ) GO exec sp_MSforeachtable "INSERT INTO #msforeach EXEC sp_spaceused '?'" GO SELECT name,rows FROM #msforeach GO

The results are as follows. Unfortunately, sp_spaceused converts all of its numeric values to variable strings for display output, but it wouldn't be too difficult to convert the temporary table columns back in order to do arithmetic on them if required.

name rows ------------------------------ ------------ authors 23 publishers 8 titles 18 titleauthor 25 stores 6 sales 21 roysched 86 discounts 3 jobs 14 pub_info 8 employee 43 (11 row(s) affected)

Complex commands

In each of the preceding examples, the command to be executed has been fairly simple. In fact, there is a limit to the size of the command parameter: 255 characters in version 6.5, 2,000 in 7.0. If you need to execute a command set that's longer than this, such as a large SELECT statement or an IF ... ELSE block, then you can use up to three command parameters, each with the same size restriction.

By default, each of these command parameters are executed as separate T-SQL batches. If an actual T-SQL command extends across two parameters, this will cause a syntax error. To avoid this, you can code `++' in the first two characters of a command to specify that it's a continuation of the previous parameter.

In the previous examples, the value of the first parameter @command1 has been provided positionally -- that is, without specifying @command1= explicitly. The additional command parameters, @command2 and @command3, are actually the third and fourth parameters positionally. The second parameter is the placeholder value. So to continue coding parameter values positionally, you have to code the second parameter -- the placeholder -- explicitly, as in the next example. (If you prefer, you can name the parameters and specify them in whatever order you like.) This example shows a large SELECT with UNIONs that's used to list all of the triggers on each table:

sp_MSforeachtable "print '--------------------------------------' print 'Triggers defined on ?' print '--------------------------------------'", "?", "SELECT tr.name,'FOR INSERT' from sysobjects so join sysobjects tr on tr.id=so.instrig where so.id=OBJECT_ID('?') UNION SELECT tr.name,'FOR UPDATE' from sysobjects so join sysobjects tr on tr.id=so.updtrig where so.id=OBJECT_ID('?')", "++UNION SELECT tr.name,'FOR DELETE' from sysobjects so join sysobjects tr on tr.id=so.deltrig where so.id=OBJECT_ID('?')", @whereand='AND (instrig+updtrig+deltrig)>0' GO

The results from the pubs database illustrate that there's only one table with triggers:

-------------------------------------- Triggers defined on dbo.employee -------------------------------------- name ------------------------------ ---------- employee_insupd FOR INSERT employee_insupd FOR UPDATE

sp_MSforeach_worker

So, how does this all work? In fact, it's implemented as two procedures. Procedure sp_MSforeachtable creates the hCForEach cursor against sysobjects and then passes all of the command strings to a second procedure sp_MSforeach_worker. This "worker" procedure does all of the work of evaluating and running the commands within a loop controlled by the cursor.

As described previously, the main command parameters use a placeholder character that's replaced by the real object name at execution time. Remember that there's a limit to the size of each command parameter. But what if this length is exceeded after the placeholder character has been evaluated? To cope with this possibility, sp_MSforeach_worker uses a work area of nine strings, each of the maximum size, into which it expands each of the command parameters at a time. In the unlikely event that the placeholder replacement uses up all of this workspace, then the procedure will fail with the message: "sp_MSforeach_worker assert failed: command too long."

The flexibility of sp_MSforeach_worker is that it can be exploited by any procedure that loops around a set of database object names. In sp_Msforeachtable, this is obviously table names, but it isn't difficult to write your own procedures that use sp_MSforeach_worker to handle other object types such as views, indexes, stored procedures, and so forth. In fact, supplied with SQL Server, there's another procedure that uses the same principle: sp_Msforeachdb, which executes a set of commands against each database in turn.

#SQLOLEDbUserProfile and sp_MSdbuserprofile

Under SQL Server 6.5, Enterprise Manager uses the sp_MSforeachdb procedure to gather basic information about each database; first, when you click on the database folder to expand it, and then again, when you use the Database Maintenance Plan wizard. Its operation depends on a special temporary table called #SQLOLEDbUserProfile. This holds a list of all databases on the server and, against each database, an indication of its availability and what permissions the current user has to access it (if you're interested, the structure and profile values are shown in the sidebar "Structure of #SQLOLEDbUserProfile in SQL Server 6.5"). Enterprise Manager clearly uses the values in this profile to determine which databases it includes in the list that you see.

The profile table is maintained with the procedure sp_MSdbuserprofile. Its default processing is to initialize the profile table by emptying it and rebuilding the list of databases. When run from the master database, it lists all databases on the server. But if the current database is any other than the master database, then it contains only that database in its list.

Unfortunately, being a local temporary table, #SQLOLEDbUserProfile is only available to the connection under which it was created. Opening a query window under Enterprise Manager or ISQL/W opens a new connection that doesn't have access to any existing profile table. To use the sp_MSforeachdb procedure, you have to create it explicitly, as follows:

create table #SQLOLEDbUserProfile (dbid int NOT NULL PRIMARY KEY, profilebits int NOT NULL) exec sp_MSdbuserprofile 'init' go

In SQL Server 7.0, #SQLOLEDbUserProfile is no longer used by sp_MSforeachdb. It's been replaced with the new has_dbaccess property and Metadata Function DATABASEPROPERTY().

sp_MSforeachdb

Procedure sp_MSforeachdb will repeat a set of commands against every database on the server. Prior to version 6.5, such a procedure was impossible, as it relies on the direct EXEC() syntax, which is the only way of allowing a database name to be parameterized in a command. It takes all of the same parameters as sp_MSforeachtable (see the sidebar "Syntax for sp_MSforeachtable" for its syntax), with the notable exception of @whereand. No database filtering facility has been provided; sp_MSforeachdb operates on all available databases to which you have access.

I first used sp_MSforeachdb when I needed to calculate the total number of database objects on a server. I'd noticed messages in the error log such as "Warning: OPEN OBJECTS parameter may be too low...Run sp_configure to increase parameter value." The solution is to increase the configuration parameter to be at least as high as the total number of database objects on the server -- that is, sum up the counts of rows in sysobjects across all databases. With more than 40 databases on the server, I didn't want to do this manually, but I found a neat solution using sp_MSforeachdb:

-- In 6.5 need #SQLOLEDbUserProfile (see above) CREATE TABLE #msforeach (name varchar(30), objects int) go exec sp_MSforeachdb "INSERT INTO #msforeach select '?',count(*) from ?..sysobjects" go select * from #msforeach compute sum(objects) go

As you can see, I used the temporary table method to force a single result set so that I could compute an overall total. The results might look something like this:

name objects ------------------------------ ----------- master 925 model 40 msdb 457 Northwind 120 pubs 89 tempdb 42 sum ========== 1673

In the preceding code, all I needed the placeholder for was to replace the database name in the qualifier for the sysobjects table. The master database was the current database throughout. Sometimes this isn't appropriate and you need a "USE database" statement to switch current database before executing the repeated command. This is no problem, as long as you note that you don't need a GO after the USE. In the following example, I wanted to identify those databases with a user named guest:

-- In 6.5 need #SQLOLEDbUserProfile (see above) exec sp_MSforeachdb "USE ? select db_name()exec sp_helpuser guest"

Because of the difference in the security models for versions 6.x and 7.0, the output from this looks slightly different. Listing 1 shows the first part of the output on a version 7.0 server.

Listing 1. The first part of the output on a 7.0 server. ------------------------------------------------- master UserName GroupName LoginName DefDBName UserID SUserID ---------- ------------ --------- --------- ------ ------- guest public NULL NULL 2 NULL ------------------------------------------------- model Server: Msg 15198, Level 16, State 1, Procedure sp_helpuser, Line 189 The name supplied (guest) is not a user, role, or aliased login.

Each to their own
I've found the MSforeach procedures to be particularly useful -- so much so that it's a shame they operate only on the database and table collections. However, thanks to the flexibility of a design that incorporates the generic sp_MSforeach_worker procedure, you can easily write your own versions against other types of database objects.

All you need to do is create your procedure with the appropriate command and placeholder parameters, declare a cursor called hCForEach that returns the names of the objects to be processed, and then call sp_MSforeach_worker. Add some error handling and you end up with something like the following procedure that operates on user view objects:

create proc sp_myforeachview @command1 varchar(255), @replacechar char(1) = '?', @command2 varchar(255) = null, @command3 varchar(255) = null, @whereand varchar(255) = null as exec("declare hCForEach cursor for select user_name(uid) + '.' + object_name(id) from sysobjects o" + " where type = 'V' " + " and o.category & 0x0002=0 " + @whereand) declare @retval int select @retval = @@error if (@retval = 0) exec @retval= sp_MSforeach_worker @command1,@replacechar, @command2,@command3 return @retval GO

The @precommand and @postcommand parameters aren't needed for sp_MSforeach_worker, so I've omitted them for simplicity (and because I rarely use them!). If you want to include them, just take a look at the code for sp_MSforeachtable in master. By creating the new procedure in master and giving it a name starting with lower case "sp_", it can then be executed from within any other database.

In the following example, sp_myforeachview is used to list all of the dependencies of each view in a database. The PRINT statement helps to separate the output:

exec sp_myforeachview "PRINT '*** ? ***' exec sp_depends '?'"

The pubs database has only one view defined, and Listing 2 shows the results under version 7.0. Under version 6.5, sp_depends doesn't show referenced columns, and the output is even smaller! To give the code a real test, try it out on the Northwind database supplied with version 7.0.

Listing 2. Output showing the results under version 7.0. *** dbo.titleview *** In the current database, the specified object references the following: name type updated selected column ---------------- ---------------- ------- -------- --------- dbo.authors user table no no au_id dbo.authors user table no no au_lname dbo.titles user table no no title_id dbo.titles user table no no title dbo.titles user table no no pub_id dbo.titles user table no no price dbo.titles user table no no ytd_sales dbo.titleauthor user table no no au_id dbo.titleauthor user table no no title_id dbo.titleauthor user table no no au_ord

foreach is for me
As long as you understand their parameters, the MSforeach procedures are simple and effective to use. They're present in versions 6.5 and 7.0, and they take the same parameters and operate in essentially the same way. However, they aren't documented and aren't guaranteed to work the same way in future releases, particularly as they were originally designed specifically for Enterprise Manager in 6.5.

Sidebar: Syntax for sp_MSforeachtable
sp_MSforeachtable command1 [,replacechar] [,command2] [,command3] [,whereand] [,precommand] [,postcommand]

where