Project No. 1. – Universal foreach aka uforeach

Hi Everyone!

This is the first post of In this post I would like to introduce my universal foreach script.

So, here is the story:

There is a very good blog post about removing duplicated indexes posted by Kimberly L. Tripp. In this post there is a reference to a very useful sp_MSforeachdb replacement made by Aaron Bertrand.

It made me start thinking and I realized that lot of time we need not just to run the same script on different databases, but to run the same script on different objects such as logins, jobs. So Aaron Bertrand’s script inspired me to write an universal foreach stored procedure.

This stored procedure is very flexible.

Here is the syntax:

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:

Let’s check some examples. You can get these examples using the @help=1 parameter as well. Pay close attention to the third example where I use sp_uforeach in nested way.


Download: sp_SQLApprenticeDOTnet_SQL2008_uforeach

I really hope that you can find this stored procedure useful. If so and you use sp_uforeach  then please drop me a mail (robertATsqlapprenticeDOTnet) and I post your real life example(s). Don’t hesitate to contact me in case of having any question or remark.



This entry was posted in Project, Script, SQL and tagged , , . Bookmark the permalink.