I have a database with thousands of stored procedures and I need to alter hundreds of them.

In order to do it faster I thought of a way to write a script that runs over all stored procedures and adds some code at the beginning (if needed).

For example, to write a script that adds SET NOCOUNT ON if not exists. So that it will alter all the stored procedures that does not have it yet.

Does it possible? If yes, can you give me some tips, guides?

I haven't tried nothing yet because I don't have any direction for doing it.



Thanks to tpet suggestion I solved it. Maybe not the best way, and I should add some more code so it will be more safe.


    Name NVARCHAR(128)

DECLARE @sp_count INT,
        @count INT = 0,
        @sp_name NVARCHAR(128),
        @text NVARCHAR(MAX);

INSERT  @sp_names
SELECT  name
FROM    sys.Procedures;

SET @sp_count = (SELECT COUNT(1) FROM sys.Procedures);

WHILE (@sp_count > @count)
    SET @count = @count + 1;
    SET @text = N'';

    SET @sp_name = (SELECT  name
                    FROM    @sp_names
                    WHERE   ID = @count);

    INSERT INTO @HelpText
    EXEC sp_HelpText @sp_name;

    SELECT  @text = COALESCE(@text + ' ' + Val, Val)
    FROM    @HelpText;

    DELETE FROM @HelpText;

    IF @text LIKE '%SET NOCOUNT ON%'
        SELECT @text;
    ELSE --Not found, should be added.

        DECLARE @Find NVARCHAR(255);
        SET @Find = 'BEGIN';

        SET @text = STUFF(@text, CHARINDEX(@Find, @text), LEN(@Find), @Find + CHAR(13) + CHAR(10) + SPACE(4) + 'SET NOCOUNT ON;');

        EXECUTE sp_executesql @text;
  • What version of SQL Server?
    – Hannah Vernon
    Commented Aug 8, 2014 at 22:39
  • 3
    This script is pretty unreliable, for a variety of reasons: (1) it relies on a single space between CREATE and PROCEDURE when in fact that could be any white space and doesn't even have to be PROCEDURE - it could be CREATE<tab>PROC (2) same for SET NOCOUNT ON (3) it assumes every procedure will have a BEGIN (4) it also assumes case insensitive (on a case sensitive server, CREATE <> create. but the metadata could be stored either way, or even CrEaTe). Commented Aug 9, 2014 at 1:30
  • 2
    Anyway you should post the "solution" as an answer, not as an edit to the question, please. Commented Aug 9, 2014 at 1:30
  • 1
    Aa long as all the procedures match your formatting rules this should be somewhat reliable. However as @aaron pointed out, the whole exercise is fraught with danger.
    – Hannah Vernon
    Commented Aug 9, 2014 at 6:11
  • 1
    You'd probably be better off using SMO for this. That easily differentiates the proc header from the body for example. You could even use TSQL100Parser. Commented Aug 9, 2014 at 11:31

2 Answers 2


Start out with getting all the procedures from sys.procedures

SELECT  * FROM sys.Procedures 

Then use EXEC sp_helptext on each to load the text of the procedures.

Search for text to add, add if needed,

Then load the updated text to a variable and execute dynamically.

  • 1
    You can read the definitions a bit more cleanly than using sp_helptext by joining in sys.sql_modules on the object_id columns (though as you'll be cursoring to make the updates anyway in the case the difference is small). Commented Aug 12, 2014 at 16:37

The above answer from @tpet is good,

I will like to improvise something to above per comment from @David to read the definitions bit more clearly..

USE <databaseName>;

SELECT o.[name]
    , m.[definition]
FROM sys.sql_modules m 
INNER JOIN sys.objects o 
    ON m.[object_id]=o.[object_id]
    AND is_ms_shipped = 0 -- to exclude system stored proc
    AND [type_desc] = 'SQL_STORED_PROCEDURE'
    AND m.[definition] not like '%SET%NOCOUNT%ON%'; -- your search


  • Here, Name and Definition both are available so, no need to run sp_helptext for each SP and this will be faster too.
  • All the Items here are without SET NOCOUNT ON in the definition, the match is done using % so that it will match Spaces as well as Tabs(per comment from @Aaron)
  • Now, just final step required to Append the text and update, though its tricky, as Appending snippet should be appropriate.
  • Here OP ask to append in the beginning only after Begin so that is possible.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.