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

  7. Msg 207, Level 16, State 1, Line 71
    Invalid column name ‘TableName’.
    Msg 207, Level 16, State 1, Line 71
    Invalid column name ‘ColumnType’.
    Msg 207, Level 16, State 1, Line 70
    Invalid column name ‘TableName’.
    Msg 207, Level 16, State 1, Line 70
    Invalid column name ‘ColumnType’.

    Sorry possibly a dumb question, but what am I doing wrong?? I get the above.

    Comment by Steve — April 28, 2015 @ 10:52 pm

    • It looks like for some reason you dropped the table #Results?
      Whenever it finds something it put it into the #Results table, and at the end basically does a SELECT from it.

      If you are using the parameter
      SET @FullRowResult = 1
      you can also decide to not even use this temp table since FullRowResult means we echo each found row (limited by @FullRowResultRows)

      Comment by fullparam — May 3, 2015 @ 5:48 pm

  8. This is genius. I have been searching and searching, and nothing has worked properly. Your solution is exactly what I need. Thank you so incredibly much!

    Comment by The Deb — August 21, 2015 @ 4:56 pm

  9. I just wanted to drop a huge THANK YOU here for this excellent script. You have saved my day searching out a needle in a giant haystack. Great work, thanks for sharing!

    Comment by John — September 4, 2015 @ 8:11 am

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

Create a free website or blog at WordPress.com.

%d bloggers like this: