Modifying sp_MSforeachdb

The other day I was faced with the task of updating some data in about 80 of around 450 similar databases in one of our customers SQL Server 2008 database hotels. Since the selectionprocess for finding the right 80 databases was too complex to fit directly into the sp_MSforeachdb syntax, I decided to modify it to suit my needs. As a precaution, I made a copy of course, and modified that.

I wanted to be able to feed a list of my own choosing to the new sp, and for me the easiest way was via a table. So first I createad a table called master.dbo.dbname with just one column, defined as varchar(250). Then I modified my copy of sp_MSforeachdb (called sp_SQLServiceforeachdb) to look like the following: USE [master] GO /****** Object:  StoredProcedure [dbo].[sp_SQLServiceforeachdb]    Script Date: 02/03/2010 14:48:42 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER OFF GO /*  * The following table definition will be created by SQLDMO at start of each connection.  * We don’t create it here temporarily because we need it in Exec() or upgrade won’t work.  */ Create proc [dbo].[sp_SQLServiceforeachdb] @command1 nvarchar(2000), @replacechar nchar(1) = N’?’,@command2 nvarchar(2000) = null, @command3 nvarchar(2000) = null,@precommand nvarchar(2000) = null, @postcommand nvarchar(2000) = null as     set deadlock_priority low /* This proc returns one or more rows for each accessible db, with each db defaulting to its own result set *//* @precommand and @postcommand may be used to force a single result set via a temp table. *//* Preprocessor won’t replace within quotes so have to use str(). */declare @inaccessible nvarchar(12), @invalidlogin nvarchar(12), @dbinaccessible nvarchar(12) select @inaccessible = ltrim(str(convert(int, 0x03e0), 11)) select @invalidlogin = ltrim(str(convert(int, 0x40000000), 11)) select @dbinaccessible = N’0x80000000´SQLDMODbUserProf_InaccessibleDb; the negative number doesn’t work in convert() */if (@precommand is not null) exec(@precommand) declare @origdb nvarchar(128) select @origdb = db_name()/* If it’s a single user db and there’s an entry for it in sysprocesses who isn’t us, we can’t use it. */    /* Create the select */exec(N’declare hCForEachDatabase cursor global for select dbname from master.dbo.dbname’ )declare @retval int select @retval = @@error if (@retval = 0) exec @retval = sys.sp_MSforeach_worker @command1, @replacechar, @command2, @command3, 1if (@retval = 0 and @postcommand is not null) exec(@postcommand)    declare @tempdb nvarchar(258) SELECT @tempdb = REPLACE(@origdb, N’]’, N’||’)exec (N’use ‘ + N'[‘ + @tempdb + N’]’) return @retval