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

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

Advertisements

14 thoughts on “Screw it, I am going to search all tables, all columns!

  1. Just a small hint… if you want to search all Views instead, this is easily possible with this.
    Just change:
    TABLE_TYPE = ‘BASE TABLE’
    into
    TABLE_TYPE = ‘VIEW’

    And BAMMMM!

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

  3. 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.

    1. 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)

  4. 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!

  5. 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!

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