Click here to Skip to main content
13,901,356 members
Click here to Skip to main content
Add your own
alternative version


22 bookmarked
Posted 14 Dec 2010
Licenced CPOL

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 database?

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.


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 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'


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


About the Author

Sandesh M Patil
Team Leader
India India
Sandesh has a 8 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.

You may also be interested in...


Comments and Discussions

GeneralMy vote of 4 Pin
Umesh AP16-Nov-15 19:21
memberUmesh AP16-Nov-15 19:21 
BugError: 1. How I find a particular column name within all tables of SQL server datbase. Pin
Member 115760992-Apr-15 3:41
memberMember 115760992-Apr-15 3:41 
QuestionHandy Query, Works Perfectly Pin
Danish Kayani7-May-14 20:26
groupDanish Kayani7-May-14 20:26 
AnswerRe: Handy Query, Works Perfectly Pin
Sandesh M Patil7-May-14 23:05
memberSandesh M Patil7-May-14 23:05 
GeneralMy vote of 5 Pin
Silvabolt12-Aug-13 5:03
memberSilvabolt12-Aug-13 5:03 
Questionvote of 5 Pin
Gonzalo F.7-Mar-13 13:42
memberGonzalo F.7-Mar-13 13:42 
GeneralReason for my vote of 5 It is nice tip Pin
Member 9016933-Oct-11 20:42
memberMember 9016933-Oct-11 20:42 
GeneralReason for my vote of 4 like it Pin
Pranay Rana22-Dec-10 22:09
professionalPranay Rana22-Dec-10 22:09 
GeneralReason for my vote of 5 A very Good Trick Pin
Vivek Johari22-Dec-10 21:48
memberVivek Johari22-Dec-10 21:48 
GeneralReason for my vote of 5 Very Handy and easy to remember. Tha... Pin
Gandalf_TheWhite22-Dec-10 2:03
professionalGandalf_TheWhite22-Dec-10 2:03 
GeneralNo problem, but thought I had been doing it wrong. Would no... Pin
Corporal Agarn15-Dec-10 8:17
professionalCorporal Agarn15-Dec-10 8:17 
GeneralThanks for correction. Pin
Sandesh M Patil15-Dec-10 5:53
memberSandesh M Patil15-Dec-10 5:53 
GeneralDo you need the space on either side of tax? Pin
Corporal Agarn15-Dec-10 5:45
professionalCorporal Agarn15-Dec-10 5:45 
Basavaraj P Biradar1-Oct-11 9:43
memberBasavaraj P Biradar1-Oct-11 9:43 
GeneralVery Handy Pin
Gandalf_TheWhite22-Dec-10 2:04
professionalGandalf_TheWhite22-Dec-10 2:04 
GeneralRe: Very Handy Pin
Sandesh M Patil22-Dec-10 2:24
memberSandesh M Patil22-Dec-10 2:24 

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.

Permalink | Advertise | Privacy | Cookies | Terms of Use | Mobile
Web06 | 2.8.190306.1 | Last Updated 23 Dec 2010
Article Copyright 2010 by Sandesh M Patil
Everything else Copyright © CodeProject, 1999-2019
Layout: fixed | fluid