SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

Get your favorite SSC scripts directly in SSMS with the free SQL Scripts addin. Search for scripts directly from SSMS, and instantly access any saved scripts in your SSC briefcase from the favorites tab.
Download now (direct download link)

Universal foreach aka sp_uforeach

By Robert Virag,


EXEC master.dbo.sp_uforeach @table_name='table name or select statement',

@column_name='existing column name', 
@where_clause='where clause only when tabla name is used', 
@command='command with ? replace character', 
@replace_character='you can define a spec character which is used for replacing the 'looping object' (default '?')
@print_command_only= 1 only print the command, 0 execute it
@print_object_name= 1 print/select the object name the script working on (def 0)
@debug=1 enable debug information

There are two options how you can provide the ‘looping objects’ which the script in the @command parameter have to run on.
First you can use an existing table name in the @table_name parameter (for example ‘sys.databases’) and in this case you can also provide a where clause without the word ‘where’ in the @where_clause parameter to filter the objects (for example ‘state = 0′).
Second, you can use a select statement with a where clause, like ‘select name from sys.databases where status = 0′. Using select statement the parameter @where_clause is ignored, but the parameter @column_name have to be provided, because the records of this column will be used as ‘looping objects’.

If you only provide the @command parameter, the stored procedure works like sp_MSforeachdb:

/*DEFAULT(foreach_db without command):*/
EXEC sp_uforeach @table_name='sys.databases', 
@print_command_only= 1

You can get help and examples using the @help=1 parameter as well.

Total article views: 1745 | Views in the last 30 days: 2
Related Articles

Introduction to ADO - The Command Object

The third article in a four part series, this week Andy shows how to use the command object to work ...


case statement and where clause

Case in where clause


SSRS 2005 conditional / on-the-fly where clause

How to call a parameter in the where clause


Conditional WHERE Clauses and Boolean Algebra

Utilize simple boolean algebra and nullable parameters to implement conditional WHERE clauses withou...


The Elusive Conditional WHERE Clause

What's this, a conditional WHERE clause that doesn't use dynamic SQL?

stored procedures