Script that alters all stored procedure on the database

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.

Solved

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.

DECLARE @HelpText TABLE
(
    Val NVARCHAR(MAX)
);

DECLARE @sp_names TABLE
(
    ID INT PRIMARY KEY IDENTITY,
    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)
BEGIN
    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%'
    BEGIN
        SELECT @text;
    END
    ELSE --Not found, should be added.
    BEGIN
        SET @text = REPLACE(@text, 'CREATE PROCEDURE', 'ALTER PROCEDURE');

        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;
    END
END

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.

StackExchange Database Administrators Q#73557, answer score: 4