[MSSQL] 3. SP 내에서 특정 문자열 찾기

1. 특정 DB 안에서만 찾을 경우

SELECT OBJECT_NAME(object_id), OBJECT_DEFINITION(object_id) 
	FROM sys.procedures 
	WHERE OBJECT_DEFINITION(object_id) LIKE '%MERGE%'

2. 장비 내에 모든 DB 대상으로 찾을 경우

DROP TABLE IF EXISTS #TblSearchProcedure
GO

SELECT TOP 0 OBJECT_NAME(object_id) AS _object_name, OBJECT_DEFINITION(object_id) as _object_definition
	INTO #TblSearchProcedure
	FROM sys.procedures 
	WHERE OBJECT_DEFINITION(object_id) LIKE N'%찾을 문자열%'

EXEC sp_MSforeachdb 'USE ? INSERT INTO #TblSearchProcedure(_object_name, _object_definition) SELECT OBJECT_NAME(object_id) AS _object_name, OBJECT_DEFINITION(object_id) as _object_definition FROM sys.procedures  WHERE OBJECT_DEFINITION(object_id) LIKE N''%찾을 문자열%'''