(SQL-Server 2017)ストアドプロシージャや関数のソースの中身をキーワード検索したい

SQL-Server 2017(Express)において、ストアドプロシージャや関数から、あるキーワードをもつソースを検索したい・・・という時があります。

たとえば、数多くのソースの中から、「GETDATE()」を使っているものだけをリストアップしたいとか。

実はこれ、外部のツールや手作業を介さずに、クエリーだけである程度やれてしまうんです。


■当該サーバーの全データベース検索

-- --------------------------------------
-- SPや関数の中身を検索
-- --------------------------------------
-- 結果格納
-- 一時テーブルが存在したら削除します
IF EXISTS(SELECT * 
            FROM tempdb.dbo.sysobjects 
           WHERE id=OBJECT_ID('tempdb.dbo.#RESULT') AND type='U')
BEGIN
    DROP TABLE #RESULT;
END; 

-- 結果格納
CREATE TABLE #RESULT (
    [DB] nvarchar(max)
   ,[名前] nvarchar(max)
   ,[種類] nvarchar(max)
   ,[作成日] datetime
   ,[更新日] datetime
);

--変数定義と条件の指定
DECLARE @DBNM nvarchar(max)
       ,@SQL  nvarchar(max)
       ,@CONDITION nvarchar(max) = 
           N'T.[definition] LIKE''%条件1%'' AND ' +
           N'T.[definition] LIKE''%条件2%''     ' +
           N''; --条件を必要なだけ記述

--当該サーバーの全DBを処理(システムデータベース除外)           
DECLARE MY_CUR CURSOR FAST_FORWARD FOR
    SELECT name 
      FROM master.sys.databases
     WHERE name NOT IN('master','tempdb','model','msdb')
     ORDER BY name;

OPEN MY_CUR;
FETCH NEXT FROM MY_CUR INTO @DBNM;
--@@@@@@@@@@[LOOP-START]@@@@@@@@@@
WHILE @@FETCH_STATUS = 0
BEGIN
    SET @SQL = 
        N'INSERT INTO #RESULT ' +
        N'SELECT ''{0}'' AS [DB] ' +
        N'    ,W.[name]  AS [名前]' +
        N'    ,W.[type_desc] AS [種類]' +
        N'    ,W.[create_date] AS [作成日]' +
        N'    ,W.[modify_date] AS [更新日]' +
        N'  FROM {0}.sys.sql_modules AS T ' +
        N'       INNER JOIN {0}.sys.objects AS W ' +
        N'           ON T.object_id=W.object_id ' +
        N' WHERE {1}; ';
    --データベース{0}、条件{1}をセットし動的SQLを実行
    SET @SQL = REPLACE(REPLACE(@SQL,'{0}',@DBNM)
               ,'{1}',@CONDITION);
    EXEC (@SQL);
    FETCH NEXT FROM MY_CUR INTO @DBNM;
END;
--@@@@@@@@@@[LOOP-END  ]@@@@@@@@@@
CLOSE MY_CUR;
DEALLOCATE MY_CUR;

SELECT [DB]
      ,[名前]
      ,[種類]
      ,[作成日]
      ,[更新日]
   FROM #RESULT
  ORDER BY [DB],[名前];

↑当該サーバーのデータベースを全てリストアップし(ただしシステムデータベースは除外します)、カーソルで1データベース毎に処理をします。

そのデータベースの中をLIKEでキーワード検索し、リストアップしたデータベースが無くなるまで繰り返す・・・という動きです。

ちなみに、条件については好きなように書けるようにしています。

↑このクエリーを使い、「GETDATE()」を使っているソースを検索してみました。

結果は上記ようになります。

格納されているデータベース名と、ソース名、作成日と更新日だけを一覧表示するようにしてあります。

ソースの中身はここを元に自分で見ないといけないですが、ソース調査作業の入口にはなると思います。

今、ASUS E203MAでやっているのですが、言ったら3万円の格安ノートPCでも、もはやSQL-Serverが快適に使える時代になってるんだなぁと感慨深いですね。