更改數據庫上所有儲存過程的腳本

我有一個包含數千個儲存過程的數據庫，我需要更改數百個。

為了更快地做到這一點，我想到了一種編寫腳本的方法，該腳本執行在所有儲存過程上並在開頭添加一些程式碼（如果需要）。

例如，編寫一個腳本，SET NOCOUNT ON如果不存在則添加。這樣它就會改變所有還沒有它的儲存過程。

有可能嗎？如果是的話，你能給我一些提示，指南嗎？

我還沒有嘗試過，因為我沒有任何方向去做。

謝謝。

解決了

感謝 tpet 的建議，我解決了它。也許不是最好的方法，我應該添加更多程式碼，這樣會更安全。

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

從獲取所有程序開始sys.procedures

SELECT  * FROM sys.Procedures 

然後使用EXEC sp_helptexton each 來載入程序的文本。

搜尋要添加的文本，根據需要添加，

然後將更新後的文本載入到變數中並動態執行。

@tpet 的上述回答很好，

根據@David 的評論，我想在上面即興創作一些東西，以便更清楚地閱讀定義。

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

現在，

• 在這裡，名稱和定義都可用，因此無需sp_helptext為每個 SP 執行，這也會更快。
• 這裡的所有項目都沒有SET NOCOUNT ON在定義中，匹配是使用完成的，%因此它會匹配（Spaces根據Tabs@Aaron 的評論）
• 現在，只是附加文本和更新所需的最後一步，儘管它很棘手，因為附加片段應該是合適的。
• 在這裡，OP 要求僅在 Begin 之後才在開頭附加，這樣就可以了。

引用自：https://dba.stackexchange.com/questions/73557