Project No. 1. – Universal foreach aka uforeach

Hi Everyone!

This is the first post of SQLApprentice.net. 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:

1
2
3
4
5
6
7
8
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:

1
2
3
4
5
6
/*DEFAULT(foreach_db without command):*/
EXEC sp_uforeach @table_name='sys.databases', 
@column_name='name', 
@where_clause='', 
@command='', 
@print_command_only= 1

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.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
/*-------Configure the filtered jobs to write to the Windows Application event log*/
EXEC master.dbo.sp_uforeach @table_name=N'msdb.dbo.sysjobs',
@column_name=N'job_id',
@where_clause=N'name like ''%TSI%''',
@command=N'USE [msdb] EXEC msdb.dbo.sp_update_job @job_id=N''?'', @notify_level_eventlog=2',
@print_command_only =1 
 
/*-------Give sysadmin role to logins which name's start with 'adm'*/
EXEC master.dbo.sp_uforeach @table_name='sys.server_principals',
@column_name='name',
@where_clause='type in (''S'',''U'') AND name LIKE ''%adm_%''',
@command='EXEC master..sp_addsrvrolemember @loginname=''?'', @rolename=N''sysadmin''',
@print_command_only =1 
 
/*-------Index maintenance in a specific database*/
EXEC master.dbo.sp_uforeach @table_name='USE AdventureWorks SELECT SCHEMA_NAME(schema_id)+''.''+name as name FROM AdventureWorks.sys.tables',
@column_name='name',
@replace_character='!',
@print_command_only = 0,
@command='USE AdventureWorks exec master.dbo.sp_uforeach @use_quotename=1,
@print_command_only=1,
@table_name=''USE AdventureWorks SELECT a.index_id, name, avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats (DB_ID(), OBJECT_ID(''''!''''),NULL, NULL, NULL) AS a
JOIN sys.indexes AS b ON a.object_id = b.object_id AND a.index_id=b.index_id AND a.index_id>0'',
@column_name=''name'',
@command=''USE [AdventureWorks] ALTER INDEX ? ON ! REORGANIZE;'''

 

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.

Leave a Reply

Your email address will not be published. Required fields are marked *

*

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>