3

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
  • What version of SQL Server? – Max Vernon Aug 8 '14 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). – Aaron Bertrand Aug 9 '14 at 1:30
  • 2
    Anyway you should post the "solution" as an answer, not as an edit to the question, please. – Aaron Bertrand Aug 9 '14 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. – Max Vernon Aug 9 '14 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. – Martin Smith Aug 9 '14 at 11:31
3

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). – David Spillett Aug 12 '14 at 16:37
1

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]
WHERE 1=1
    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

Now,

  • 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, privacy policy and cookie policy

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