Search text in stored procedure in SQL Server

I created a procedure to search text in procedures/functions, tables, views, or jobs. The first parameter @search is the search criterion, @target the search target, i.e., procedures, tables, etc. If not specified, search all. @db is to specify the database to search, default to your current database. Here is my query in dynamic SQL.

ALTER PROCEDURE [dbo].[usp_find_objects]
(
@search VARCHAR(255),
@target VARCHAR(255) = NULL,
@db VARCHAR(35) = NULL
)
AS

SET NOCOUNT ON;

DECLARE @TSQL NVARCHAR(MAX), @USEDB NVARCHAR(50)

IF @db <> '' SET @USEDB = 'USE ' + @db
ELSE SET @USEDB = ''

IF @target IS NULL SET @target = ''

SET @TSQL = @USEDB + '

DECLARE @search VARCHAR(128)
DECLARE @target VARCHAR(128)

SET @search = ''%' + @search + '%''
SET @target = ''' + @target + '''

IF @target LIKE ''%Procedure%'' BEGIN
SELECT o.name As ''Stored Procedures''
FROM SYSOBJECTS o
INNER JOIN SYSCOMMENTS c ON o.id = c.id
WHERE c.text LIKE @search
AND o.xtype IN (''P'',''FN'')
GROUP BY o.name
ORDER BY o.name
END

ELSE IF @target LIKE ''%View%'' BEGIN
SELECT o.name As ''Views''
FROM SYSOBJECTS o
INNER JOIN SYSCOMMENTS c ON o.id = c.id
WHERE c.text LIKE @search
AND o.xtype = ''V''
GROUP BY o.name
ORDER BY o.name
END

/* Table - search table name only, need to add column name */
ELSE IF @target LIKE ''%Table%'' BEGIN
SELECT t.name AS ''TableName''
FROM sys.columns c
JOIN sys.tables t ON c.object_id = t.object_id
WHERE c.name LIKE @search
ORDER BY TableName
END

ELSE IF @target LIKE ''%Job%'' BEGIN
SELECT j.job_id,
s.srvname,
j.name,
js.step_id,
js.command,
j.enabled
FROM [msdb].dbo.sysjobs j
JOIN [msdb].dbo.sysjobsteps js
ON js.job_id = j.job_id
JOIN master.dbo.sysservers s
ON s.srvid = j.originating_server_id
WHERE js.command LIKE @search
END

ELSE BEGIN
SELECT o.name As ''Stored Procedures''
FROM SYSOBJECTS o
INNER JOIN SYSCOMMENTS c ON o.id = c.id
WHERE c.text LIKE @search
AND o.xtype IN (''P'',''FN'')
GROUP BY o.name
ORDER BY o.name

SELECT o.name As ''Views''
FROM SYSOBJECTS o
INNER JOIN SYSCOMMENTS c ON o.id = c.id
WHERE c.text LIKE @search
AND o.xtype = ''V''
GROUP BY o.name
ORDER BY o.name

SELECT t.name AS ''Tables''
FROM sys.columns c
JOIN sys.tables t ON c.object_id = t.object_id
WHERE c.name LIKE @search
ORDER BY Tables

SELECT j.name AS ''Jobs''
FROM [msdb].dbo.sysjobs j
JOIN [msdb].dbo.sysjobsteps js
ON js.job_id = j.job_id
JOIN master.dbo.sysservers s
ON s.srvid = j.originating_server_id
WHERE js.command LIKE @search
END
'

EXECUTE sp_executesql @TSQL

>**Update:**
If you renamed a procedure, it only updates `sysobjects` but not `syscomments`, which keeps the old name and therefore that procedure will not be included in the search result unless you drop and recreate the procedure.