spSearchOnAllDB: SQL Stored Procedure to Search an Entire Database

David Meego - Click for blog homepageThis is a reposting of an article I originally wrote on my Developing for Dynamics GP blog.

From the Useful SQL Scripts Series.

This stored procedure, originally written by Oufimtsev Gleb from Moscow, allows you to search all columns in all tables in a database for the occurrence of a particular string or value.  Wild card characters such as those supported by the LIKE keyword can be used.

For example: _ to wild card a single character, and % to wild card a number of characters.

Once you have run the script to create the stored procedure you can execute it to look for data, here are some examples:

exec spSearchOnAllDB ‘Sugar%’
exec spSearchOnAllDB ‘%soft%’
exec spSearchOnAllDB ‘_5234_57%’, 1
exec spSearchOnAllDB M_cro_oft

This script is available from other locations around the Internet, but the one attached has had some changes to make it more suitable for working in a Dynamics GP environment, such as granting of access to DYNGRP. It has also been updated to handle table and column long names with spaces or reserved words in them.

Note: To include a single quote (‘) character in the search string, you will need to replace the single quote (‘) with 2 single quotes in a row (”).  Doubling up a single quote will prevent the early termination of the search parameter. Once passed to the code, the updated version of the stored procedure will handle doubling up again for the dynamically created scripts allowing the dynamic scripts to run correctly. 

The script is available as an attachment at the bottom of this post. Last Modified: 26-Jul-2013.

Note: Uncomment the line granting Execute permissions to DYNGRP if you want other non ‘sa’ users to be able to use the stored procedure in a Dynamics related database.

Also have a look at the follow up post, Updated: spSearchOnAllDB: SQL Stored Procedure to Search an Entire Database, which as a Locate Value application that you can use.


24-Jun-2009: Added hint about searching for single quotes.

19-Jul-2013: Updated stored procedure code to handle table and column long names with spaces or reserved words in them. Thanks Eduardo for pointing out the problem.

26-Jul-2013: Updated stored procedure code to handle doubling up of the single quote for use with the dynamically created scripts.

27-Sep-2013: Added link to follow up article.


This article was originally posted on the Developing for Dynamics GP Blog and has been reposted on http://www.winthropdc.com/blog.

20 thoughts on “spSearchOnAllDB: SQL Stored Procedure to Search an Entire Database

  1. This is great!  I hope I can get it to work.
    I ran the sp using the example
    exec  spSearchOnAllDB ‘Sugar%’
    Results were returned from the Sugar% but then I also received this message:
    Msg 102, Level 15, State 1, Line 1
    Incorrect syntax near ‘Name’.
    What have I done wrong?


  2. Leslie,
    I didn’t have any issues on my TWO database or DYNAMICS db.  I’m wondering if you don’t have a table with a column called "Name" in it as I don’t see otherwise in the SQL how this couldn’t work.
    if you add right above the exec (sql) the line:
     print @sql
    Then on the Messages tab you’ll see all the SQL statements being generated.  Search for "Name" in them and I suspect you’ll see it is there and sql is unhappy about that because it probably is a reserved word.
    We might need to add [] around the column and table fields in the SQL script.


  3. Hi! First of all, thanks for the script, it was very usefull.
    The problem reported is related with "long names" in tables or column tables. You must add "[" and "]" to queries inside of stored proceduce, to all @tbl and @col variables.
    This is the unofficial path:  🙂
     if @OutFullRecords=0
       set @sql='insert into #tbl_res (TableName,ColumnName,Id,ColumnValue) '
               +'select '+char(39)+@tbl+char(39)+', '
                         +char(39)+@col+char(39)+', '
       if @id_present=1 set @sql=@sql+'IDENTITYCOL, '
                   else set @sql=@sql+'NULL, '
       set @sql=@sql+'convert(varchar(7500),['+@col+']) '
                    +'from ['+@tbl+'] (nolock) '
                    +'where convert(varchar(8000),['+@col+']) like '+char(39)+@phrase+char(39)
     if @OutFullRecords=1
       set @sql='if exists (select * from '+@tbl+' (nolock) '
                          +'where convert(varchar(8000),['+@col+']) like '+char(39)+@phrase+char(39)+') '
               +'select '+char(39)+@tbl+char(39)+' TableName, '+char(39)+@col+char(39)+' ColumnName, * '
               +'from ['+@tbl+'] (nolock) where convert(varchar(8000),['+@col+']) like '+char(39)+@phrase+char(39)


  4. Thanks Eduardo!
    On hindsight that makes perfect sense but it wasn't obvious to me that really was our issue.


  5. You're welcome, and thanks to you!
    I forgot to add the fix in table name with the option "OutFullRecords"
     if @OutFullRecords=1
       set @sql='if exists (select * from ['+@tbl+'] (nolock) '


  6. Why include the tip about single quotes in a search string needing to be extended to four quotes instead of just doing this in the script?


  7. Hi Marc
    Good idea, but as you need to pass the single quotes inside a string to the stored procedure, you will have to double up the single quotes for the parameter yourself.
    I have updated the script to double up again for the dynamically created code and updated the article accordingly.


  8. Thanks David!
    I find that doing this sort of thing as a stored procedure could encourage people to load the stored procedure onto customer production servers which I don't like, I prefer to just leave it as a query that can be run without creating additional SQL objects


Please post feedback or comments

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 )

Google photo

You are commenting using your Google 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 )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.