sp_SearchStoredProcedures - Search all stored procedures for a string value

sp_SearchStoredProcedures.sql

USE [master]
GO
/****** Object:  StoredProcedure [dbo].[sp_SearchStoredProcedures]    Script Date: 05/17/2013 08:02:05 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

if exists (select * from sysobjects where id = object_id(N'[dbo].[sp_SearchStoredProcedures]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sp_SearchStoredProcedures]
GO

CREATE Procedure [dbo].[sp_SearchStoredProcedures]
  @SearchValue varchar(max)
AS

DECLARE @sql VARCHAR(MAX), @ServerName VARCHAR(MAX), @dbName VARCHAR(MAX)

SET @sql = 'SELECT DISTINCT ''local'' as server, o.name AS Object_Name,o.type_desc, m.definition, 0 as synonym ' +
           'FROM sys.sql_modules m INNER JOIN sys.objects o ON m.object_id=o.object_id '+
	       'WHERE  m.definition LIKE ''%'+@SearchValue+'%''';

DECLARE TableCursor CURSOR FOR

SELECT DISTINCT
SUBSTRING(COALESCE(PARSENAME(base_object_name,4),@@servername), 
	  CHARINDEX('.', COALESCE(PARSENAME(base_object_name,4),@@servername)) + 1, 
	  LEN(COALESCE(PARSENAME(base_object_name,4),@@servername))) as ServerName,
COALESCE(PARSENAME(base_object_name,3),DB_NAME(DB_ID())) AS dbName
FROM sys.synonyms 
WHERE COALESCE(PARSENAME(base_object_name,3),DB_NAME(DB_ID())) <> DB_NAME()
ORDER BY serverName,dbName

OPEN TableCursor
FETCH NEXT FROM TableCursor INTO @ServerName, @dbName

WHILE @@FETCH_STATUS = 0
BEGIN

   set @sql = @sql + 
   'UNION ' +
   'SELECT '''+@ServerName+''' as server, o.name AS Object_Name,o.type_desc, m.definition, 1 as synonym ' +
   'FROM   ['+@ServerName+'].['+@dbName+'].sys.sql_modules m INNER JOIN sys.objects o ON m.object_id=o.object_id ' +
   'WHERE  m.definition LIKE ''%'+@SearchValue+'%'' '
  
   FETCH NEXT FROM TableCursor INTO @ServerName, @dbName
END

set @sql = @sql + 'ORDER BY synonym, server, Object_name;'
CLOSE TableCursor
DEALLOCATE TableCursor

exec (@sql)
GO

EXEC sys.sp_MS_marksystemobject [sp_SearchStoredProcedures]
GO