Search for a string in all tables of SQL Server Database

To search the whole database for a string in the tables. This stored procedure will return the tables and result set with the matching search string.

4.5 Star
42,863 times
Add to favorites
E-mail Twitter Digg Facebook
Sign in to ask a question

  • SearchTMP left behind
    1 Posts | Last post March 12, 2019
    • I'm having the same issue that "Grinder" reported on January 12, 2018: a table named dbo.SearchTMP is created. Our production environment is locked down, so this will throw a run-time error. 
      I made an unsuccessful attempt to change the name of this table to #SearchTMP. This includes adding a reference to TempDB:
      changed to: 
      IF OBJECT_ID('TempDB..#SearchTMP','U') IS NOT NULL
      but no banana!
  • Enhanced to filter by column and work better on case sensitive databases
    1 Posts | Last post September 29, 2018
    • The SQL code is too long to paste here, so I linked it on gist
  • Simplicity?
    1 Posts | Last post August 24, 2018
    • Hello! 
      I am totally new to SQL though I worked already a while with PowerShell (2.0 - 5.0) and I am perplexed about the massive code to be used for a (imho) simple task!
      Is SQL always such a codeblaster? I mean, searching for a string in tables should not end up in more than 2-3 lines, using wildcards etc.
      Seeing that script I'm almost giving up to continue working with SQL scripts...LOL
      Thanks for providing, just for admins like me, it is a showstopper. At least I'd like to understand something from a script I intent to work with ;)
      All the best
  • What versions of SQL Server are supported
    3 Posts | Last post July 09, 2018
    • What versions of SQL Server are supported?
    • I've been working on SS2K14 but see no reason why it shouldn't work on most versions.
      Despite being prefixed with 'sp_' don't be misled into thinking it should be created in master. It should be created in the database you want to search.
    • Hi Tim GJ,
        This code should work on any version. As you have rightly mentioned , this proc should be compiled in the database in which it needs to be compiled.
  • Unable to find 5EABDAFA-389A-11E7-B66A-001B21A26318 in tables
    2 Posts | Last post April 12, 2018
    • Hi Sorna,
      Thanks for the script, when searching all tables for '5EABDAFA-389A-11E7-B66A-001B21A26318'
      I get a No Matches are found in this database. 
      Is there anything special, I would need to do when searching for GIUD?
    • Added  the "uniqueidentifier" to the line below in the script  and now it finds the GIUD.
    in ('varchar','char','nvarchar','nchar','text','uniqueidentifier')
  • SearchTMP left behind
    1 Posts | Last post January 12, 2018
    • The procedure creates and leaves behind the table SearchTMP wherever it's run.  This has caused some problems.  I tried changing it to make SearchTMP a temporary table but haven't been able to get it to work yet.
      We are locking down our environments so I will not have permission to create objects.  I'm hoping temporary tables will let me get around that issue.
      Great script except for the breadcrumbs.
  • Searching Chinese character
    2 Posts | Last post December 07, 2017
    • i have created a table with a col using nvarchar to store traditional chinese charater, however can't find
    • Hi ,
         The latest fix has this feature in the script.
  • Great script, but it does not work with unicode charachters.
    2 Posts | Last post December 07, 2017
    • I made some modifications to make it work:
      SearchString NVARCHAR(500)
      ,WHEREClause    NVARCHAR(MAX)
      ,SQLStatement   NVARCHAR(MAX)
      SELECT @SQL = 'SELECT N''' + REPLACE(@SearchStr,',','''as SearchString UNION SELECT ''') + ''''
      SELECT '[' + SC.Name + ']' + ' LIKE N''' + REPLACE(SearchSTR.SearchString,'''','''''') + ''' OR ' + CHAR(10)
    • Thanks Arash for the feedback and the code. Applied the required changes to code for Unicode search. This feedback gave me a thought on how a case sensitive search can be done. Added a new parameter to provide the collation to be used for the search. By providing a case sensitive collation the search can be performed with case.
      Thanks again for the feedback to make the code better.
      Warm Regards
  • Issue with apostrophe
    3 Posts | Last post November 08, 2017
    • Hello Sorna,
      I love this tool. It works great, but...I'm trying to do a search for a string that includes an apostrophe. I'm getting errors and escaping with a second apostrophe doesn't work. I can see where you accounted for the comma in a string. I don't have enough experience to try to modify the procedure to account for an apostrophe. Can you add this to the list of feature requests?
    • Just wanted to say "thanks" :)
    • Hi BMWest,
         This issue is fixed. Some how this comment slipped through my review. Thanks for the feedback.
  • Great Script, but I made a modification
    3 Posts | Last post November 08, 2017
    • I have a database with multiple schema names.  To narrow the search Iadded logic to select tables from only one schema.  It's an optional parameter.  If it is set, it adds "schema." as a prefix to the table names.
    • Hi ,
        Very good suggestion indeed. Will include this in the script.
    • Hi Rob,
        This feature of searching by Schema name has been added in the script. Thanks for your feedback to make the script better.
1 - 10 of 27 Items