How to find column or text in entire stored procedure of a database?

Introduction

Sometimes it happens that you would like to know how many stored procedures there are using a particular text or table name or column name. 

Background

Suppose you have a large database which has many stored procedures and due to some client requirements you need to change a  particular column or hard coded condition but you are not aware of how many places it is used. You need to find all the stored procedure first and then replace or remove that particular condition or column according to your needs.

So you are thinking of what is the best way to find it.

Below I am writing two possible solutions. By using the below two sample queries you can easily achieve this.

Suppose I want to search "Indiandotnet" in all the stored procedures.

Using the code

BEGIN TRY
  DECLARE @strColumn VARCHAR(1000)
  SET @strColumn =’indiandotnet’
  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

Option 2:

BEGIN TRY
  DECLARE @strColumn VARCHAR(1000)

  SET @strColumn =’Indiandotnet’

  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

It proved very useful to me and I hope it will be helpful to you somewhere.