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
Best Answer
Start out with getting all the procedures 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.