[SQL Server] 全データベースから特定文字列を含むストアドプロシージャ(STORED PROCEDURE)を検索

inno
2017-03-15 11:44 2234 0
たまに全データベースから特定の文字列を含むストアアドプロシージャ(STORED PROCEDURE)を検索したい時があります。
簡単に検索できる方法を共有します。

下記のSQLを一括で実行することで検索できます。
検索語は「SET @SearchWord = 'innoya'」となっている「innoya」に検索語を入れて下記のSQLを一括で実行してください。

USE [master]
GO

DECLARE @SearchWord nvarchar(100)

-- #### 検索語 (以下の単語が含まれている STORED PROCEDURE を検索します)
SET @SearchWord = 'innoya'


-- #### 検索結果を保存する一時テーブルを作成
CREATE TABLE #TempData
(
DBNamevarchar(100)
, SpNamevarchar(100)
, ObjectTypevarchar(100)
, CreateDatedatetime
, ModifyDatedatetime
, SpDescnvarchar(max)
)


DECLARE @StrSQL nvarchar(max)
DECLARE @DBName varchar(100)

DECLARE DB_Cursor CURSOR LOCAL FAST_FORWARD FOR

SELECT name FROM msdb.sys.databases WITH (NOLOCK)
 
OPEN DB_Cursor
FETCH NEXT FROM DB_Cursor
INTO @DBName
WHILE @@FETCH_STATUS = 0
BEGIN

SET @StrSQL = 'USE ' + @DBName
SET @StrSQL = @StrSQL + '
INSERT INTO #TempData
SELECT ''' + @DBName + '''
, A.name
, A.type_desc
, A.create_date
, A.modify_date
, B.definition
FROM sys.objects AS A JOIN sys.sql_modules AS B
ON A.object_id = B.object_id
WHERE A.schema_id = 1 AND type = ''P'' 
and definition  like ''%' + @SearchWord + '%'''


EXEC (@StrSQL)
PRINT ('######################### ' + @DBName + ' #### 検索完了 ####')

FETCH NEXT FROM DB_Cursor
INTO @DBName

END

CLOSE DB_Cursor
DEALLOCATE DB_Cursor


-- #### 検索結果
SELECT * FROM #TempData ORDER BY DBName, SpName

-- #### 一時テーブル削除
DROP TABLE #TempData

検索結果



コメント