Data Management

Create custom looping procedures in SQL Server 2005

Tim Chapman shows how to create system stored procedures that will allow you to loop through and execute statements against any type of object in SQL Server 2005.

In my previous SQL Server article, I discussed how you can use two undocumented system stored procedures to your advantage when it comes to executing ad-hoc statements against a large number of tables or databases. I'll expand upon that idea and show how to create system stored procedures that will allow you to loop through and execute statements against any type of object in SQL Server 2005.

An example

My example will involve two stored procedures, both of which are alterations of the sp_msforeachdb and sp_msforeachtable system stored procedures.

First, I need to create a helper procedure, which I named sp_MSForEach_Helper. This procedure is almost exactly the same as its cousin sp_MSForEach_Worker; I only changed the names of the cursor objects involved. The following script will create this stored procedure:

set ANSI_NULLS ON

set QUOTED_IDENTIFIER ON

go

use master

GO

/*

 * This is the worker proc for all of the "for each" type procs.  Its function is to read the

 * next replacement name from the cursor (which returns only a single name), plug it into the

 * replacement locations for the commands, and execute them.  It assumes the cursor "hCForEach***"

 * has already been opened by its caller.

 * worker_type is a parameter that indicates whether we call this for a database (1) or for a table (0)

 */

CREATE PROCEDURE [dbo].[sp_MSForEach_Helper]

            @command1 nvarchar(2000), @replacechar nchar(1) = N'?', @command2 nvarchar(2000) = null, @command3 nvarchar(2000) = null

as
            create table #qtemp (  /* Temp command storage */

                        qnum                                       int                                            NOT NULL,

                        qchar                                       nvarchar(2000)           COLLATE database_default NULL

            )
            set nocount on

            declare @name nvarchar(517), @namelen int, @q1 nvarchar(2000), @q2 nvarchar(2000)

            declare @q3 nvarchar(2000), @q4 nvarchar(2000), @q5 nvarchar(2000)

            declare @q6 nvarchar(2000), @q7 nvarchar(2000), @q8 nvarchar(2000), @q9 nvarchar(2000), @q10 nvarchar(2000)

            declare @cmd nvarchar(2000), @replacecharindex int, @useq tinyint, @usecmd tinyint, @nextcmd nvarchar(2000)

            declare @namesave nvarchar(517), @nametmp nvarchar(517), @nametmp2 nvarchar(258)
            declare @local_cursor cursor

                        set @local_cursor = hCForEachObject

           

            open @local_cursor

            fetch @local_cursor into @name
            /* Loop for each database */

            while (@@fetch_status >= 0) begin

                        /* Initialize. */
      /* save the original dbname */

      select @namesave = @name

                        select @useq = 1, @usecmd = 1, @cmd = @command1, @namelen = datalength(@name)

                        while (@cmd is not null) begin                        /* Generate @q* for exec() */

                                    /*

                                     * Parse each @commandX into a single executable batch.

                                     * Because the expanded form of a @commandX may be > OSQL_MAXCOLLEN_SET, we'll need to allow overflow.

                                     * We also may append @commandX's (signified by '++' as first letters of next @command).

                                     */

                                    select @replacecharindex = charindex(@replacechar, @cmd)

                                    while (@replacecharindex <> 0) begin
            /* 7.0, if name contains ' character, and the name has been single quoted in command, double all of them in dbname */

            /* if the name has not been single quoted in command, do not doulbe them */

            /* if name contains ] character, and the name has been [] quoted in command, double all of ] in dbname */

            select @name = @namesave

            select @namelen = datalength(@name)

            declare @tempindex int

            if (substring(@cmd, @replacecharindex - 1, 1) = N'''') begin

               /* if ? is inside of '', we need to double all the ' in name */

               select @name = REPLACE(@name, N'''', N'''''')

            end else if (substring(@cmd, @replacecharindex - 1, 1) = N'[') begin

               /* if ? is inside of [], we need to double all the ] in name */

               select @name = REPLACE(@name, N']', N']]')

            end else if ((@name LIKE N'%].%]') and (substring(@name, 1, 1) = N'[')) begin

               /* ? is NOT inside of [] nor '', and the name is in [owner].[name] format, handle it */

               /* !!! work around, when using LIKE to find string pattern, can't use '[', since LIKE operator is treating '[' as a wide char */

               select @tempindex = charindex(N'].[', @name)

               select @nametmp  = substring(@name, 2, @tempindex-2 )

               select @nametmp2 = substring(@name, @tempindex+3, len(@name)-@tempindex-3 )

               select @nametmp  = REPLACE(@nametmp, N']', N']]')

               select @nametmp2 = REPLACE(@nametmp2, N']', N']]')

               select @name = N'[' + @nametmp + N'].[' + @nametmp2 + ']'

            end else if ((@name LIKE N'%]') and (substring(@name, 1, 1) = N'[')) begin

               /* ? is NOT inside of [] nor '', and the name is in [name] format, handle it */

               /* j.i.c., since we should not fall into this case */

               /* !!! work around, when using LIKE to find string pattern, can't use '[', since LIKE operator is treating '[' as a wide char */

               select @nametmp = substring(@name, 2, len(@name)-2 )

               select @nametmp = REPLACE(@nametmp, N']', N']]')

               select @name = N'[' + @nametmp + N']'

            end

            /* Get the new length */

            select @namelen = datalength(@name)
            /* start normal process */

                                                if (datalength(@cmd) + @namelen - 1 > 2000) begin

                                                            /* Overflow; put preceding stuff into the temp table */

                                                            if (@useq > 9) begin

                                                                        close @local_cursor

                                                                                    deallocate hCForEachObject

                                                                                   

                                                                        raiserror 55555 N'sp_MSforeach_worker assert failed:  command too long'

                                                                        return 1

                                                            end

                                                            if (@replacecharindex < @namelen) begin

                                                                        /* If this happened close to beginning, make sure expansion has enough room. */

                                                                        /* In this case no trailing space can occur as the row ends with @name. */

                                                                        select @nextcmd = substring(@cmd, 1, @replacecharindex)

                                                                        select @cmd = substring(@cmd, @replacecharindex + 1, 2000)

                                                                        select @nextcmd = stuff(@nextcmd, @replacecharindex, 1, @name)

                                                                        select @replacecharindex = charindex(@replacechar, @cmd)

                                                                        insert #qtemp values (@useq, @nextcmd)

                                                                        select @useq = @useq + 1

                                                                        continue

                                                            end

                                                            /* Move the string down and stuff() in-place. */

                                                            /* Because varchar columns trim trailing spaces, we may need to prepend one to the following string. */

                                                            /* In this case, the char to be replaced is moved over by one. */

                                                            insert #qtemp values (@useq, substring(@cmd, 1, @replacecharindex - 1))

                                                            if (substring(@cmd, @replacecharindex - 1, 1) = N' ') begin

                                                                        select @cmd = N' ' + substring(@cmd, @replacecharindex, 2000)

                                                                        select @replacecharindex = 2

                                                            end else begin

                                                                        select @cmd = substring(@cmd, @replacecharindex, 2000)

                                                                        select @replacecharindex = 1

                                                            end

                                                            select @useq = @useq + 1

                                                end

                                                select @cmd = stuff(@cmd, @replacecharindex, 1, @name)

                                                select @replacecharindex = charindex(@replacechar, @cmd)

                                    end
                                    /* Done replacing for current @cmd.  Get the next one and see if it's to be appended. */

                                    select @usecmd = @usecmd + 1

                                    select @nextcmd = case (@usecmd) when 2 then @command2 when 3 then @command3 else null end

                                    if (@nextcmd is not null and substring(@nextcmd, 1, 2) = N'++') begin

                                                insert #qtemp values (@useq, @cmd)

                                                select @cmd = substring(@nextcmd, 3, 2000), @useq = @useq + 1

                                                continue

                                    end
                                    /* Now exec() the generated @q*, and see if we had more commands to exec().  Continue even if errors. */

                                    /* Null them first as the no-result-set case won't. */

                                    select @q1 = null, @q2 = null, @q3 = null, @q4 = null, @q5 = null, @q6 = null, @q7 = null, @q8 = null, @q9 = null, @q10 = null

                                    select @q1 = qchar from #qtemp where qnum = 1

                                    select @q2 = qchar from #qtemp where qnum = 2

                                    select @q3 = qchar from #qtemp where qnum = 3

                                    select @q4 = qchar from #qtemp where qnum = 4

                                    select @q5 = qchar from #qtemp where qnum = 5

                                    select @q6 = qchar from #qtemp where qnum = 6

                                    select @q7 = qchar from #qtemp where qnum = 7

                                    select @q8 = qchar from #qtemp where qnum = 8

                                    select @q9 = qchar from #qtemp where qnum = 9

                                    select @q10 = qchar from #qtemp where qnum = 10

                                    truncate table #qtemp

                                    exec (@q1 + @q2 + @q3 + @q4 + @q5 + @q6 + @q7 + @q8 + @q9 + @q10 + @cmd)

                                    select @cmd = @nextcmd, @useq = 1

                        end /* while @cmd is not null, generating @q* for exec() */
                        /* All commands done for this name.  Go to next one. */

                        fetch @local_cursor into @name

            end /* while FETCH_SUCCESS */

            close @local_cursor

                        deallocate hCForEachObject

                       

            return 0
GO

Now all I need to do is make the stored procedure a system stored procedure by using the following code:

EXECUTE sp_MS_MarkSystemObject 'sp_MSForEach_Helper'
GO

Once my helper procedure is created, I can start working on the procedure to which I'll be passing my commands. The script below is an adaptation of the Microsoft procedure sp_MSForEachTable. The procedure allows an object type parameter, so you can choose the type of object on your system that you want to loop through. Below is a listing of the available object types:

  • AF: Aggregate function (CLR)
  • C: CHECK constraint
  • D: DEFAULT (constraint or stand-alone)
  • F: FOREIGN KEY constraint
  • PK: PRIMARY KEY constraint
  • P: SQL stored procedure
  • PC: Assembly (CLR) stored procedure
  • FN: SQL scalar function
  • FS: Assembly (CLR) scalar function
  • FT: Assembly (CLR) table-valued function
  • R: Rule (old-style, stand-alone)
  • RF: Replication-filter-procedure
  • S: System base table
  • SN: Synonym
  • SQ: Service queue
  • TA: Assembly (CLR) DML trigger
  • TR: SQL DML trigger
  • IF: SQL inline table-valued function
  • TF: SQL table-valued-function
  • U: Table (user-defined)
  • UQ: UNIQUE constraint
  • V: View
  • X: Extended stored procedure
  • IT: Internal table

I also swapped out the EXECUTE() command for the stored procedure sp_executesql.

set ANSI_NULLS ON

set QUOTED_IDENTIFIER ON

SET ARITHABORT ON

go
use master

go

CREATE PROCEDURE sp_ForEachObject

(

            @command1 NVARCHAR(2000), @replacechar nchar(1) = N'?',

            @objecttype NVARCHAR(2) = null, @command2 NVARCHAR(2000) = null,

            @command3 NVARCHAR(2000) = null, @whereand NVARCHAR(2000) = null,

            @precommand NVARCHAR(2000) = null, @postcommand NVARCHAR(2000) = null

)

AS

BEGIN
            DECLARE @mscat NVARCHAR(12)

            DECLARE @sql NVARCHAR(4000)

            DECLARE @retval INT
            IF @precommand IS NOT NULL
                        EXECUTE sp_executesql @precommand
            SET @sql = N'DECLARE hCForEachObject CURSOR GLOBAL FOR SELECT ''['' + syso.name + '']'' FROM sys.all_objects syso '
            IF @objecttype IS NOT NULL
                        SET @sql = @sql + ' WHERE syso.type = ''' + @objecttype + ''''
            EXECUTE sp_executesql @sql
            SET @retval = @@error
            IF (@retval = 0)         

            BEGIN

                        EXECUTE @retval = sp_MSForEach_Helper

                        @command1 = @command1,

                        @replacechar = @replacechar,

                        @command2 = @command2,

                        @command3 = @command3

            END
            IF (@retval = 0 and @postcommand is not null)
            EXECUTE(@postcommand)
            RETURN @retval

END

go
EXECUTE sp_MS_marksystemobject 'sp_ForEachObject'

Once I execute this script, I am able to execute sp_ForEachObject in any database on my server and execute commands on any of the object types listed above. For example, the following code loops through all user-defined tables in my database and prints them to the screen:

EXECUTE sp_ForEachObject

@command1 = 'select ''?''',

@objecttype = 'U'

Another great use for this stored procedure is to print out the text of all of my stored procedures in my database. Here is the code:

sp_ForEachObject

@command1 = 'PRINT OBJECT_DEFINITION(OBJECT_ID(''?'')) + CHAR(10) + ''GO''',

@objecttype = 'P'

Start creating

These custom loop procedures are a powerful and flexible addition to your SQL Server 2005 toolkit. For instance, it would be very simple to alter sp_ForEachObject so that you can loop through databases, server triggers, or logins. Give it a try!

Tim Chapman a SQL Server database administrator and consultant who works for a bank in Louisville, KY. Tim has more than eight years of IT experience, and he is a Microsoft certified Database Developer and Administrator. If you would like to contact Tim, please e-mail him at chapman.tim@gmail.com.

---------------------------------------------------------------------------------------

Get more SQL Server tips in your inbox

TechRepublic's Servers and Storage newsletter, delivered on Monday and Wednesday, offers tips that will help you manage and optimize your data center. Automatically sign up today!

About

Tim Chapman is a SQL Server MVP, a database architect, and an administrator who works as an independent consultant in Raleigh, NC, and has more than nine years of IT experience.

0 comments

Editor's Picks