Find Text in sub procedures SO.Type = ‘P’ means Procedure, other system object types are:
P – Procedures
FN – Scalar-Valued Functions
TF – Table-valued functions
V – Views
D – Database Diagrams
X – Tables
I have commented that line in bold and it will search everything for entered string value in this case.
To search text in a sub-procedure or otherwise
CREATE PROCEDURE [dbo].[Find_Text_In_SP]
@StringToSearch varchar(100)
AS
SET @StringToSearch = '%' +@StringToSearch + '%'
SELECT Distinct SO.Name
FROM sysobjects SO (NOLOCK)
INNER JOIN syscomments SC (NOLOCK) on SO.Id = SC.ID
--AND SO.Type = 'P'
AND SC.Text LIKE @stringtosearch
ORDER BY SO.Name
To search for a string within subprocedure name:
CREATE PROCEDURE [dbo].[Find_SPName_With_Text]
@StringToSearch varchar(100)
AS
SET @StringToSearch = '%' + @StringToSearch + '%'
SELECT DISTINCT SO.NAME
FROM SYSOBJECTS SO (NOLOCK)
WHERE SO.TYPE = 'P'
AND SO.NAME LIKE @StringToSearch
ORDER BY SO.Name
-SB
