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
(76)
41,998 times
Add to favorites
Databases
12/7/2017
E-mail Twitter del.icio.us 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:
      
      IF OBJECT_ID('SearchTMP','U') IS NOT NULL
      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
      https://gist.github.com/chrispret/959420810973b60d2c3b7f136f5f1cc0
      
  • 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
      Sascha
  • 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.
      
      Thanks
      
      Sorna
  • 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?
      
      Thanks
      
      Jay 
    • Added  the "uniqueidentifier" to the line below in the script  and now it finds the GIUD.
      
      STY.name 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.
      
      Thanks
      
      Sorna
  • 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:
      
      1)
      SearchString NVARCHAR(500)
      
      2)
      ,WHEREClause    NVARCHAR(MAX)
      ,SQLStatement   NVARCHAR(MAX)
      
      3)
      DECLARE @SQL NVARCHAR(MAX)
      
      4)
      SELECT @SQL = 'SELECT N''' + REPLACE(@SearchStr,',','''as SearchString UNION SELECT ''') + ''''
      
      5)
      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
      
      Sorna
  • 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?
      
      Thanks 
    • Just wanted to say "thanks" :)
    • Hi BMWest,
      
         This issue is fixed. Some how this comment slipped through my review. Thanks for the feedback.
      
      Regards
      
      Sorna
  • 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.
      
      Thanks.
      Rob
    • Hi ,
      
        Very good suggestion indeed. Will include this in the script.
      
      Thanks
      
      Sorna
      
    • Hi Rob,
      
        This feature of searching by Schema name has been added in the script. Thanks for your feedback to make the script better.
      
      Thanks
      
      Sorna
1 - 10 of 27 Items