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