Universal foreach aka sp_uforeach
Syntax:
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',
@column_name='name',
@where_clause='',
@command='',
@print_command_only= 1
You can get help and examples using the @help=1 parameter as well.
/*============================================================================
File: sp_SQLApprentice_SQL2008_uforeach.sql
Summary: Flexible universal foreach, default behavior: sp_MSforeachdb
Date: 2012.08
Version: v3.0 (with debug option)
Tested: SQL Server 2008 Version
------------------------------------------------------------------------------
Written by Robert Virag
Special thanks to Gábor Kiss
Inspired by Aaron Bertrand's script (http://www.mssqltips.com/sqlservertip/2201/making-a-more-reliable-and-flexible-spmsforeachdb/)
For more scripts and sample code, check out
http://www.SQLApprentice.net
THIS CODE AND INFORMATION ARE PROVIDED "AS IS" WITHOUT WARRANTY OF
ANY KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED
TO THE IMPLIED WARRANTIES OF MERCHANTABILITY AND/OR FITNESS FOR A
PARTICULAR PURPOSE.
============================================================================*/
USE master
go
if OBJECTPROPERTY(OBJECT_ID('sp_uforeach'), 'IsProcedure') = 1
drop procedure sp_uforeach
go
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE procedure [dbo].[sp_uforeach]
@table_name NVARCHAR(MAX) = N'sys.databases',
@column_name NVARCHAR(MAX) = N'name',
@where_clause NVARCHAR(MAX) = N'',
@command NVARCHAR(MAX) = N'',
@print_command_only BIT = 1,
@print_object_name BIT = 0,
@replace_character NCHAR(1) = N'?',
@help BIT = 0,
@use_quotename BIT = 0,
@debug BIT = 0
AS
BEGIN
SET NOCOUNT ON;
IF @help = 1
BEGIN
PRINT 'EXEC master.dbo.sp_uforeach @table_name=''table name or select statement'', '+ CHAR(13) + CHAR(10)
+ '@column_name=''existing column name'', '+ CHAR(13) + CHAR(10)
+ '@where_clause=''where clause can be used only when an existing table name is used'', '+ CHAR(13) + CHAR(10)
+ '@command=''command with ? replace character'', '+ CHAR(13) + CHAR(10)
+ '@replace_character=''you can define a spec character which is used for replacing the ''looping object'' (default ''?'')'+ CHAR(13) + CHAR(10)
+ '@print_command_only= 1 only print the command, 0 execute it'+ CHAR(13) + CHAR(10)
+ '@print_object_name= 1 print/select the object name the script working on (def 0)'+ CHAR(13) + CHAR(10)
+ '@debug=1 enable debug information' + CHAR(13) + CHAR(10)+ CHAR(13) + CHAR(10)
+ 'DEFAULT(foreach_db without command):'+ CHAR(13) + CHAR(10)
+ 'EXEC sp_uforeach @table_name=''sys.databases'', '+ CHAR(13) + CHAR(10)
+ '@column_name=''name'', '+ CHAR(13) + CHAR(10)
+ '@where_clause='''', '+ CHAR(13) + CHAR(10)
+ '@command='''', '+ CHAR(13) + CHAR(10)
+ '@print_command_only= 1' + CHAR(13) + CHAR(10)+ CHAR(13) + CHAR(10)
PRINT 'Usage Tipps: ' + CHAR(13) + CHAR(10)
+ '/*-------Configure the filtered jobs to write to the Windows Application event log*/'+ CHAR(13) + CHAR(10)
+ 'EXEC master.dbo.sp_uforeach @table_name=N''msdb.dbo.sysjobs'','+ CHAR(13) + CHAR(10)
+ '@column_name=N''job_id'','+ CHAR(13) + CHAR(10)
+ '@where_clause=N''name like ''''%TSI%'''''','+ CHAR(13) + CHAR(10)
+ '@command=N''USE [msdb] EXEC msdb.dbo.sp_update_job @job_id=N''''?'''', @notify_level_eventlog=2'','+ CHAR(13) + CHAR(10)
+ '@print_command_only =1 '+ CHAR(13) + CHAR(10)+ CHAR(13) + CHAR(10)
+ '/*-------Give sysadmin role to logins which name''s start with ''adm''*/' + CHAR(13) + CHAR(10)
+ 'EXEC master.dbo.sp_uforeach @table_name=''sys.server_principals'','+ CHAR(13) + CHAR(10)
+ '@column_name=''name'','+ CHAR(13) + CHAR(10)
+ '@where_clause=''type in (''''S'''',''''U'''') AND name LIKE ''''%adm_%'''''','+ CHAR(13) + CHAR(10)
+ '@command=''EXEC master..sp_addsrvrolemember @loginname=''''?'''', @rolename=N''''sysadmin'''''','+ CHAR(13) + CHAR(10)
+ '@print_command_only =1 '+ CHAR(13) + CHAR(10)+ CHAR(13) + CHAR(10)
+ '/*-------Index maintenance in a specific database*/'+ CHAR(13) + CHAR(10)
+ 'EXEC master.dbo.sp_uforeach @table_name=''USE AdventureWorks SELECT SCHEMA_NAME(schema_id)+''''.''''+name as name FROM AdventureWorks.sys.tables'','+ CHAR(13) + CHAR(10)
+ '@column_name=''name'',' + CHAR(13) + CHAR(10)
+ '@replace_character=''!'','+ CHAR(13) + CHAR(10)
+ '@print_command_only = 0,'+ CHAR(13) + CHAR(10)
+ '@command=''USE AdventureWorks exec master.dbo.sp_uforeach @use_quotename=1,'+ CHAR(13) + CHAR(10)
+ '@print_command_only=1,'+ CHAR(13) + CHAR(10)
+ '@table_name=''''USE AdventureWorks SELECT a.index_id, name, avg_fragmentation_in_percent'+ CHAR(13) + CHAR(10)
+ 'FROM sys.dm_db_index_physical_stats (DB_ID(), OBJECT_ID(''''''''!''''''''),NULL, NULL, NULL) AS a'+ CHAR(13) + CHAR(10)
+ 'JOIN sys.indexes AS b ON a.object_id = b.object_id AND a.index_id=b.index_id AND a.index_id>0'''','+ CHAR(13) + CHAR(10)
+ '@column_name=''''name'''','+ CHAR(13) + CHAR(10)
+ '@command=''''USE [AdventureWorks] ALTER INDEX ? ON ! REORGANIZE;'''''''+ CHAR(13) + CHAR(10)+ CHAR(13) + CHAR(10)
RETURN
END
DECLARE @sql NVARCHAR(MAX)
DECLARE @object NVARCHAR(300)
DECLARE @temp_table_name NVARCHAR(255)
DECLARE @table_name_old NVARCHAR(MAX)
DECLARE @inside_tmp_cmd NVARCHAR(MAX)
DECLARE @DBNAME NVARCHAR(255)
/*Creating temporary table from the select statement|START*/
IF @table_name LIKE '%SELECT %' OR @table_name LIKE '%select %'
BEGIN
SET @where_clause = N''
SET @temp_table_name = '[##' + CONVERT(NVARCHAR(255),NEWID()) + ']'
/*DEBUG START*/
IF @debug=1
BEGIN
SET @table_name_old = @table_name
SET @table_name = 'SELECT @DBNAME_OUT=DB_NAME() ' + REPLACE(@table_name,'from', 'into ' + @temp_table_name +' from')
EXEC sp_executesql @table_name, @paramdefinition=N'@DBNAME_OUT NVARCHAR(255) OUTPUT',@DBNAME_OUT=@DBNAME OUTPUT
SELECT @DBNAME AS 'Creating the temporary table on this database from the ''select'' statement:'
SELECT @table_name_old AS 'Original query'
SELECT @table_name AS 'Inside query'
SELECT @temp_table_name AS 'Temp table'
SET @inside_tmp_cmd = 'select * from ' + @temp_table_name
EXEC sp_executesql @inside_tmp_cmd
END
ELSE
BEGIN
SET @table_name = REPLACE(@table_name,'from', 'into ' + @temp_table_name +' from')
EXEC sp_executesql @table_name
END
/*DEBUG END*/
SET @table_name = @temp_table_name
END
/*Creating temporary table from the selcet statement|END*/
CREATE TABLE #objects(myobject NVARCHAR(300));
SET @sql = N'SELECT '
+ @column_name
+ ' FROM '
+ @table_name
+ CASE WHEN @where_clause <> ''
THEN ' WHERE ' + @where_clause
ELSE ''
END
INSERT #objects EXEC sp_executesql @sql;
/*DEBUG START*/
IF @debug=1
BEGIN
SET @inside_tmp_cmd = 'SELECT @DBNAME_OUT=DB_NAME()'
EXEC sp_executesql @inside_tmp_cmd, @paramdefinition=N'@DBNAME_OUT NVARCHAR(255) OUTPUT',@DBNAME_OUT=@DBNAME OUTPUT
SELECT @DBNAME AS 'Running the inside select on this database'
SELECT @sql AS 'Inside sql statement'
SELECT 'Content of the inside #object table' AS 'Info msg'
SELECT * from #objects
END
/*DEBUG END*/
DECLARE c CURSOR
LOCAL FORWARD_ONLY STATIC READ_ONLY
FOR SELECT CASE WHEN @use_quotename = 1
THEN
QUOTENAME(myobject)
ELSE
myobject
END
FROM #objects;
OPEN c;
FETCH NEXT FROM c INTO @object;
WHILE @@FETCH_STATUS = 0
BEGIN
SET @sql = REPLACE(@command, @replace_character, @object);
/*Print the executable commands plus aditional information|START*/
IF @print_command_only = 1
BEGIN
PRINT '/* For table: ' + @table_name + ', colunm: ' + @column_name + ', value: ' + @object + ': */'
+ CHAR(13) + CHAR(10) + CHAR(13) + CHAR(10)
+ @sql
+ CHAR(13) + CHAR(10) + CHAR(13) + CHAR(10);
END
/*Print the executable commands plus aditional information|END*/
ELSE
BEGIN
/*Print the objects*/
--PRINT @sql;
IF @print_object_name=1
BEGIN
SELECT @object
PRINT @object
END
/*Execute the command with the defined parameters*/
EXEC sp_executesql @sql;
END
FETCH NEXT FROM c INTO @object;
END
CLOSE c;
DEALLOCATE c;
DROP TABLE #objects;
IF @table_name=@temp_table_name
BEGIN
SET @inside_tmp_cmd = 'DROP TABLE ' + @temp_table_name
EXEC sp_executesql @inside_tmp_cmd
END
END
GO
exec sys.sp_MS_marksystemobject 'sp_uforeach'
go
