Click here to Skip to main content
15,867,704 members
Articles / Database Development / SQL Server / SQL Server 2008
Tip/Trick

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

Rate me:
Please Sign up or sign in to vote.
4.91/5 (6 votes)
11 Nov 2013CPOL 12.4K   6   4
Find column or Text in entire stored procedure

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

SQL
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:

SQL
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.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


Written By
Team Leader
India India
I am Rajat Jaiswal from India. I am working as a Technology specialist in one of the reputed company in India with 12+ years of experience. I am a Microsoft Data Platform MVP & Alibaba Cloud MVP.
I have always had an affinity to learn something new in technology. Database, Web development, Cloud computing are the areas of my interests always.
I have been a regular contributor to technologies with more than 300+ tips. Learning & Sharing is one of my aims always. Writing blogs, delivering sessions, helping on forums is my hobby.

Comments and Discussions

 
QuestionA tool that uses this technique Pin
DaveDbViewSharp13-Nov-13 11:54
DaveDbViewSharp13-Nov-13 11:54 
AnswerRe: A tool that uses this technique Pin
Rajat-Indiandotnet13-Nov-13 20:27
Rajat-Indiandotnet13-Nov-13 20:27 
SuggestionTool to search SQL Server objects Pin
mgoad9912-Nov-13 9:12
mgoad9912-Nov-13 9:12 
GeneralMy vote of 5 Pin
Md. Shafiuzzaman11-Nov-13 22:40
professionalMd. Shafiuzzaman11-Nov-13 22:40 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.