how to find column or text in entire stored procedure?

Hello Friends,

Sometime we want to search a particular column or some text in entire stored procedures. For this we can utilize following two simple approaches

Approach 1:-

BEGIN TRY

  DECLARE @strColumn VARCHAR(1000)

  SET @strColumn =’Rajat’

  SELECT DISTINCT o.name

  FROM sys.syscomments c

  INNER JOIN sys.objects o ON o.object_Id = c.Id

                 AND o.type =’P’

  WHERE text like ‘%’ + @strColumn +’%’

  ORDER BY o.NAME

END TRY

BEGIN CATCH

  SELECT ERROR_LINE(),ERROR_NUMBER(),ERROR_MESSAGE()

END CATCH

Approach 2:-

BEGIN TRY

  DECLARE @strColumn VARCHAR(1000)

  SET @strColumn =’Rajat’

  SELECT SPECIFIC_NAME

  FROM INFORMATION_SCHEMA.ROUTINES

  WHERE ROUTINE_TYPE= ‘PROCEDURE’

  AND ROUTINE_DEFINITION LIKE ‘%’ + @strColumn +’%’

  ORDER BY SPECIFIC_NAME

END TRY

BEGIN CATCH

  SELECT ERROR_LINE(),ERROR_NUMBER(),ERROR_MESSAGE()

END CATCH

Hope this will helpful to you somewhere.

 Thanks

Rajat Jaiswal