Caveman's Blog

My commitment to learning.

SQL Server: how to search for a table name or a column name or a given stored procedure name

with 10 comments


This article demonstrates how the Microsoft SQL Server database can be queried to perform the following tasks:

1. fetch a list of tables.
2. fetch a list of Columns.
3. fetch a list of Stored Procedures

The results can refined further by including other search criteria in the where clause of each query.

--Query to fetch the list of table that match a pattern
select * from information_schema.tables where table_name like '%Employee%'

--Query to fetch the list of Columns (with table info) that match a pattern.
select * from information_schema.columns where column_name like '%EmpId%'

--Query to fetch the list of Stored Procedures that match a pattern.
select ROUTINE_SCHEMA, ROUTINE_NAME, ROUTINE_DEFINITION from INFORMATION_SCHEMA.ROUTINES
where ROUTINE_TYPE='PROCEDURE' and routine_name like '%MyProc%'

Check out my other “How to Search” posts:

SQL Server: How to search for text/phrase in a Stored Procedure?

SQL Server: How to search for a number in a string?

Reference:
[1] MSDN Online

Advertisements

Written by cavemansblog

February 26, 2009 at 6:02 pm

10 Responses

Subscribe to comments with RSS.

  1. What do you consider your greatest achievement?

    Smith

    May 20, 2010 at 10:02 am

  2. asp.net, c#,javascript…

    […]SQL Server: how to search for a table name or a column name or a given stored procedure name « Caveman's Blog[…]…

    asp.net, c#,javascript

    September 29, 2011 at 5:52 am

    • It may help you…

      select ROUTINE_SCHEMA, ROUTINE_NAME, ROUTINE_DEFINITION from INFORMATION_SCHEMA.ROUTINES

      where ROUTINE_TYPE=’PROCEDURE’ and ROUTINE_DEFINITION like ‘%table_name%’

      Upendra

      October 11, 2011 at 12:00 am

  3. search for people free…

    […]SQL Server: how to search for a table name or a column name or a given stored procedure name « Caveman's Blog[…]…

    search for people free

    November 15, 2011 at 7:04 pm

  4. q8…

    […]SQL Server: how to search for a table name or a column name or a given stored procedure name « Caveman's Blog[…]…

    q8

    December 23, 2011 at 9:34 pm

  5. […] SQL Server: How to search for a number in a string? 2. SQL Server: how to search for a table name or a column name or a given stored procedure name? Share this:StumbleUponDiggRedditLike this:LikeBe the first to like this […]

  6. hi

    select Location from jobdetails
    where Location like ‘chennai%’

    this’s my procedure

    and i want result from this

    chennai, mumbai, pune
    chennai
    chennai

    plz rpy the how to find out the location search in multiple filed

    By
    bala

    bala

    April 5, 2012 at 2:51 am

  7. […] Fonte: https://cavemansblog.wordpress.com/2009/02/26/sql-how-to-2/ […]

  8. very helpfull…thanks bro..

    dhima

    January 2, 2014 at 2:13 am

  9. Sometimes information_schema is taken from some default system database, so adding prefix is way to go:
    [MyDatabase1].[information_schema]…

    Dzmitry Lahoda

    October 29, 2014 at 5:18 am


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: