September 7, 2012

Screw it, I am going to search all tables, all columns!

Filed under: Scripting, SQL Server — fullparam @ 7:21 pm

Ever had the need to find out where data is stored in your MS SQL Database?
I sometimes do and I find it often faster to simply search every table and every column within these table than to try and hunt down the schema definitions. I get a coffee while the script crunches away.  The @SearchStrColumnName option is there mostly for when you search integers, as you may get too many false positives.

Edit: GitHub Gist works awesome, will have to do that for the previous posts too! Just paste the URL, and wordpress will do the rest, in this case display a fully highlighted T-SQL code.
Edit 4/8/2013 – @SearchStrColumnName wasn’t properly escaped before and this parameter didn’t really work.
Edit 4/9/2013 – Added parameter @FullRowResult, this will cause it to return the full row for each hit. This is usefull when you need to lookup or find related info to the search term.
Edit 4/12/2013 – Added parameter @SearchStrTableName, to limit in what tables we are going to search.
Edit 5/2/2013 – Now also searching in type uniqueidentifier (GUID)
Edit 5/22/2013 – Additional numeric data types will be searched.
Edit 1/7/2014 – Can now also search timestamp value (known as rowversion)
Edit 4/18/2014 – Added a Top parameter that works together with @FullRowResult. This is to help limit the return if a search string is found in a table too often


  1. Just a small hint… if you want to search all Views instead, this is easily possible with this.
    Just change:

    And BAMMMM!

    Comment by fullparam — April 23, 2013 @ 3:52 pm

  2. My good sr..
    For sure you gain your spot in heaven for that!

    Comment by Andre Triches — July 30, 2013 @ 8:06 pm

    • Excellent, this saves me a ton of time. Thank you for posting!

      Comment by Dave — April 17, 2014 @ 3:34 pm

  3. Thank you! very useful :)

    Comment by Maria J. Santos — April 17, 2014 @ 2:35 pm

  4. I had to add “,@FullRowResultRows Bit” behind the @Declare to make it work, beyond that it works like advertised

    Comment by Rick — June 20, 2014 @ 9:10 am

    • Absolutely right Rick. I am unsure how I missed that. Must have been a copy paste issue :)
      Edit: It’s now getting fixed.

      Comment by fullparam — June 20, 2014 @ 3:30 pm

  5. hello is it possible to searh a null value ?

    Comment by Ahmet — July 24, 2014 @ 11:02 am

  6. AWESOME mate, saved me hours of work. Hats off.

    Comment by Tom — January 6, 2015 @ 9:14 pm

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

The WordPress Classic Theme. Blog at WordPress.com.


Get every new post delivered to your Inbox.

%d bloggers like this: