Search String in Stored Procedure/View/Function

   
    --Search String in Stored Procedures
   
    SELECT [name]
    FROM sys.procedures
    WHERE OBJECT_DEFINITION(OBJECT_ID) LIKE '%patientid%';
   
    GO
   
    --EXEC Find_InSP 'tblpatient'
     CREATE PROCEDURE dbo.Find_InSP
     @string NVARCHAR(MAX)
     AS
    
     BEGIN
    
     SET NOCOUNT ON;
     SELECT
       cmd = N'EXEC sp_helptext '''
           + QUOTENAME(OBJECT_SCHEMA_NAME([object_id]))
           + '.' + QUOTENAME([name]) + ''';'
     FROM
       sys.procedures
     WHERE
       OBJECT_DEFINITION([object_id]) LIKE N'%' + @string + '%'
     ORDER BY
     cmd;
    
     END
  

 --Search String in Stored Procedure/View/Function
   
    SELECT OBJECT_NAME([object_id])
    FROM sys.sql_modules
    WHERE [definition] LIKE '%patientid%';
   
   
    GO
   
 --EXEC Find_InModule 'tblpatient'
 CREATE PROCEDURE dbo.Find_InModule
 @string NVARCHAR(MAX)
 AS

 BEGIN
 SET NOCOUNT ON;
    SELECT
       cmd = N'EXEC sp_helptext '''
           + QUOTENAME(OBJECT_SCHEMA_NAME([object_id]))
           + '.' + QUOTENAME(OBJECT_NAME([object_id])) + ''';'
    FROM
       sys.sql_modules
    WHERE
       [definition] LIKE N'%' + @string + '%'
    ORDER BY
    cmd;
    END