Click here to Skip to main content
Click here to Skip to main content

How to search a column name within all tables of a database and how to search stored procedures containing a particular text

, 23 Dec 2010
Rate this:
Please Sign up or sign in to vote.
How to search a column name within all tables of a database and how to search stored procedures containing a particular text
1. How I find a particular column name within all tables of SQL server datbase.
 
For example, I have a database named Organisation. I have more than one table where tax_id column is present.
 
Most of the time, we have to find such a column from the whole database. The solution is provided below:
 
select table_name,column_name from information_schema.columns
where column_name like '%tax%'
 
Here I am searching for column_name which contains tax within its name. It will return all the tables and respective columns containing tax.
 
Example:
 

Table_Name Column_name
t_Customer tax_id
t_Employee tax_number
t_Balance_sheet tax_percentage
 
2. How to search stored procedures containing a particular text?
 
Below is the the solution. Suppose I need to find tax_id from all stored procedures. The below query will return all stored procedures containing text tax_id.
 
select routine_name, routine_definition
      from information_schema.routines
      where routine_definition like '%tax_id%'
      and routine_type='procedure'

License

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

About the Author

$andesh M Patil
Software Developer (Senior)
India India
Sandesh has a 6 years of total professional experience in a software development. He first handled the computer in his school days when he was in 7th std working on Lotus. In summer vacation, the school authorities allowed him and other students to use the computers for practicing Lotus and playing different games Smile | :) .
 
He has done computer engineering. Currently he is working in Net 4.0 framework. Even though he is using .Net 4.0 framework for development but still he has not got a chance to work with newer technologies like MVC, WCF etc. However he always try to learn these technologies from his end and eagerly waiting for a chance to work with the newer technologies like MVC, WCF and Silverlight etc.

Comments and Discussions

 
QuestionHandy Query, Works Perfectly PingroupAboutCprogramming7-May-14 20:26 
AnswerRe: Handy Query, Works Perfectly Pinpremium$andesh M Patil7-May-14 23:05 
GeneralMy vote of 5 PinmemberSilvabolt12-Aug-13 5:03 
Questionvote of 5 PinmemberMember 30474617-Mar-13 13:42 
GeneralReason for my vote of 5 It is nice tip PinmemberMember 9016933-Oct-11 20:42 
GeneralReason for my vote of 4 like it PinmemberPranay Rana22-Dec-10 22:09 
GeneralReason for my vote of 5 A very Good Trick PinmemberVivek Johari22-Dec-10 21:48 
GeneralReason for my vote of 5 Very Handy and easy to remember. Tha... PinmemberGandalf - The White22-Dec-10 2:03 
GeneralNo problem, but thought I had been doing it wrong. Would no... Pinmemberdjj5515-Dec-10 8:17 
GeneralThanks for correction. PinmemberSChristmas15-Dec-10 5:53 
GeneralDo you need the space on either side of tax? Pinmemberdjj5515-Dec-10 5:45 
BugUsing INFORMATION_SCHEMA.ROUTINES View is risky [modified] Pinmemberbiradar261-Oct-11 9:43 
GeneralVery Handy PinmemberGandalf - The White22-Dec-10 2:04 
GeneralRe: Very Handy PinmemberSChristmas22-Dec-10 2:24 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    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 | Mobile
Web01 | 2.8.140721.1 | Last Updated 23 Dec 2010
Article Copyright 2010 by $andesh M Patil
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid