Universal foreach aka sp_uforeach

, 2013-09-24 (first published: 2013-09-10)

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

Rate

4 (1)

Share

Share

Rate

4 (1)

Related content

End to End Server Disk Space reporting within SQL Server

This procedure using OLE object calls to obtain server disk space values and writes them to a table. It then creates an HTML report based on the contents of the table and then , via a SQL Agent job, sends out colour coded emails relating to how much drive space is left… this is very handy!!!!

2 (2)

noamco36

2016-03-16 (first published: 2016-02-25)

983 reads

Do you regularly run queries and scripts in SSMS?

SQL Server Management Studio is where most SQL scripts and queries are run, and although it does a decent job, it doesn’t provide much help storing, sharing and managing the scripts particularly in the context of a team. Red Gate is considering building functionality into SSMS to help its users consume and share queries and scripts. Please help us by completing this short survey to help us define the requirements.

Press Release

2013-04-12

3,832 reads

Grouped Failover, a 2008R2 version of Availability Groups

One of the new cool features in SQL 2012 is the SQL Server Availability groups. In other words being able to failover a group of databases which are logically connected. i.e. SharePoint databases. Well, it is also possible to do that in SQL 2008 (R2). It’s called a Grouped Failover.

5 (1)

Remko de Boer

2013-01-24 (first published: 2013-01-16)

549 reads