Script to do Text Search Across all Stored Procedures in a SQL Server Database

DECLARE @RoutineCOUNT INT =0
, @RoutineName VARCHAR(255)
, @count int =1
, @SearchText VARCHAR(255) = 'Address'

DECLARE @Routines TABLE
(
ID INT
, NAME VARCHAR(255)
)

INSERT INTO @Routines
SELECT
ROW_NUMBER() OVER (ORDER BY ROUTINE_NAME DESC) AS RNO
, ROUTINE_NAME
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_TYPE = 'PROCEDURE'

DECLARE @Temp TABLE
(

Defination VARCHAR(MAX) NULL
)

DECLARE @RoutineDef TABLE
(
ID INT PRIMARY KEY IDENTITY(1,1),
NAME VARCHAR(255),
Defination VARCHAR(MAX) NULL
)

SELECT @RoutineCOUNT = MAX(ID) from @Routines

WHILE (@count <= @RoutineCOUNT)
BEGIN

SELECT @RoutineName = NAME from @Routines
WHERE ID = @COUNT;

INSERT INTO @Temp
EXEC sp_helpText @RoutineName;

INSERT INTO @RoutineDef (NAME, Defination)
SELECT @RoutineName,DEFINATION FROM @Temp
WHERE DEFINATION LIKE '%' + @SearchText + '%'

DELETE FROM @Temp;

SET @count = @count + 1;
--BREAK;
END

SELECT DISTINCT NAME FROM @RoutineDef
SELECT * FROM @RoutineDef