Click here to Skip to main content
12,300,260 members (57,200 online)
Click here to Skip to main content
Add your own
alternative version

Stats

7.2K views
6 bookmarked
Posted

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

, 11 Nov 2013 CPOL
Rate this:
Please Sign up or sign in to vote.
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

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.

License

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

Share

About the Author

Rajat-Indiandotnet
Technical Lead
India India
No Biography provided

You may also be interested in...

Comments and Discussions

 
QuestionA tool that uses this technique Pin
DaveDbViewSharp13-Nov-13 11:54
memberDaveDbViewSharp13-Nov-13 11:54 
AnswerRe: A tool that uses this technique Pin
Rajat-Indiandotnet13-Nov-13 20:27
memberRajat-Indiandotnet13-Nov-13 20:27 
SuggestionTool to search SQL Server objects Pin
mgoad9912-Nov-13 9:12
membermgoad9912-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.

| Advertise | Privacy | Terms of Use | Mobile
Web02 | 2.8.160525.2 | Last Updated 11 Nov 2013
Article Copyright 2013 by Rajat-Indiandotnet
Everything else Copyright © CodeProject, 1999-2016
Layout: fixed | fluid