Sandesh M Patil">
Click here to Skip to main content
15,069,297 members
Articles / Database Development / SQL Server
Tip/Trick
Posted 14 Dec 2010

Stats

310.4K views
22 bookmarked

How to Search a Column Name Within All Tables of a Database and How to Search Stored Procedures Containing a Particular Text

Rate me:
Please Sign up or sign in to vote.
4.88/5 (24 votes)
23 Dec 2010CPOL
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:

SQL
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 solution. Suppose I need to find tax_id from all stored procedures. The below query will return all stored procedures containing text tax_id.

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

Share

About the Author

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

 
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
MemberDanish 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 
BugUsing INFORMATION_SCHEMA.ROUTINES View is risky Pin
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.