SQL Tips by Namwar Rizvi

January 8, 2009

Search all columns of all tables for a particular value

Filed under: How To,Object Search,tips,TSQL — namwar @ 7:30 PM
Tags: , , , , ,

Several times I have came across with the question that how can I search all columns of all tables in a SQL Server database for a particular value. Unfortunately, you can not perform this query directly by any already provided system table or object. 

There are some fundamental points to remember:

 

  1. You have to iterate the list of tables and for each table you have to iterate each column and perform an exist query against it. 
  2. You will need three types of queries due to the data types i.e. string, number and date and time.
I have found an excellent article here which provides a good explanation along with the necessary sql script. I would recommend every reader to read this article.

 

Advertisements

6 Comments »

  1. […] 2008, Search columns — namwar @ 8:50 am Tags: SSMS, Tools In my one of earlier articles here I wrote about a way to find any value in any database column. Today, I found a great free tool, […]

    Pingback by Search anything in your Database schema right from SSMS « SQL Tips by Namwar Rizvi — March 29, 2010 @ 8:50 AM | Reply

  2. […] my one of earlier articles here I wrote about a way to find any value in any database column. Today, I found a great free tool, […]

    Pingback by Search anything in your Database schema right from SSMS — March 29, 2010 @ 8:54 AM | Reply

  3. I wanted create search system from sql data. How retrive data from multipal table without join. Because No common field in table. I have Many table.

    Please help me.

    Comment by om prakash — March 30, 2010 @ 6:39 AM | Reply

  4. Why not simply create a series of selects?

    select ‘select ‘ + o.name + ‘ from ‘ + c.name + ‘ where ‘ + o.name + ‘ = !;GO;’
    from sys.all_columns c inner join sys.all_objects o on c.object_id = o.object_id
    where type_desc not in (‘SYSTEM_TABLE’,’SQL_INLINE_TABLE_VALUED_FUNCTION’)

    The GO keeps one failure from making the whole transaction abort. If you don’t find the value at first then simply start correcting errors and hopefully you will find it. Keep in mind, you don’t need all the queries to work–just the one that finds the value of interest!

    Comment by Joe Magura — August 20, 2012 @ 3:00 PM | Reply

  5. Or, you can use my query here, should be simpler then having to create sProcs for each DB you want to search:
    http://fullparam.wordpress.com/2012/09/07/fck-it-i-am-going-to-search-all-tables-all-collumns/

    Comment by fullparamo Egeter — November 27, 2012 @ 4:08 PM | Reply

  6. Appreciating the hard work you put into your blog and detailed information you
    offer. It’s awesome to come across a blog every once in a while that isn’t the same unwanted rehashed information.

    Excellent read! I’ve bookmarked your site and I’m including
    your RSS feeds to my Google account.

    Comment by travis county divorce cases — February 22, 2013 @ 2:25 AM | Reply


RSS feed for comments on this post. TrackBack URI

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

Blog at WordPress.com.

%d bloggers like this: