全ストアドを対象に文字列を置換する方法について

質問

こんばんわ。
SQLServer2008にて1データベース内の全ストアドを対象に、
ストアドプロシージャ記述内で特定の文字列を検索し、置換する方法を考えています。

以下のようなsqlで、ストアド内に文字列があるものを検索することはできたのですが、
ここから先、「★★★」を「○○○」にREPLACEする方法がわかりません。

select routine_name from information_schema.routines
where routine_definition LIKE '%★★★%'
and routine_type='procedure'

以下のようなsqlで更新しようとしたら、エラーが発生しました。

update information_schema.routines set
routine_definition = REPLACE(routine_definition, '★★★', '○○○')
where routine_name = 'XXX'
(error：システム カタログへのアドホック更新は許可されません。)

不定期にデータベース名を変更することがあり、ストアドの数が多いため、
一括で変換できる方法がないかと探しています。
手作業で変更していく他ないでしょうか。
なにかヒントだけでもご教示いただけると嬉しいです。
よろしくお願いします。

回答No.1

Siegrune

information_schemaの中身はシステムの情報なのでupdate文なんかで変えちゃいけません。
・・・不整合が起こってシステムが異常な動作をしても知りませんよ、って内容なので。
で、エラーになるんでしょう。

routine_definitionだけテキストファイルに出力して、メモ帳かなにかで
「CRAEATE PROCEDURE 」を「ALTER PROCEDURE 」に
「★★★」を「○○○」に一括置き換えして
sqlcmd等で実行すればそれでしたい作業は終わりのはずですが。

## とここまで書いて、ちょっと甘いことに気づいた。
・sqlcmdで接続するデータベースをきちんと指定するか、出力結果の先頭にuse等を追加して指定する。
・各alter文の最後にgoがないとだめなので、

SELECT ROUTINE_DEFINITION + CHAR(13) + CHAR(10) + 'GO' + CHAR(13)
+ CHAR(10) AS Expr1
FROM INFORMATION_SCHEMA.ROUTINES
WHERE (ROUTINE_DEFINITION LIKE '%★★★%')
AND (ROUTINE_TYPE = 'procedure')
とselect文を変更する。

なお、select句の中を、
REPLACE(REPLACE(ROUTINE_DEFINITION, 'CREATE PROCEDURE ', 'ALTER PROCEDURE ')
,'★★★', '○○○')
とするだけでメモ帳なんか使わないって方法でも大丈夫のような気がしますが、
'★★★'と'○○○'の内容次第。
'★★★'でPROで'○○○'がXYZとしたら、ALTER PROCEDUREが、ALTER XYZCEDUREになってしまって
全部エラー。。。
ということも考慮する必要はあります。