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_nameNVARCHAR(MAX) = N'sys.databases',
@column_nameNVARCHAR(MAX) = N'name',
@where_clauseNVARCHAR(MAX) = N'',
@commandNVARCHAR(MAX) = N'',
@print_command_onlyBIT=1,
@print_object_name BIT = 0,
@replace_characterNCHAR(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 @sqlNVARCHAR(MAX)
DECLARE @objectNVARCHAR(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

Rate

4 (1)

Share

Share

Rate

4 (1)