spSearchOnAllDB: SQL Stored Procedure to Search an Entire Database


David MeegoFrom 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.

David

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.

spSearchOnAllDB.zip

Comments (18)

  1. Steve says:

    Thanks!  Perfect timing!  I literally needed to do this today, and was wondering about how to do it.

  2. Based on the success of the "Resolving Security Issues in Dynamics GP" located here, Microsoft Dynamics

  3. Based on the success of the "Resolving Security Issues in Dynamics GP" located here, Microsoft

  4. Hussain Al-Yousef says:

    Thank you David, very good script.

    Hussain Al-Yousef

    hyou91@gmail.com

  5. Leslie Vail says:

    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?

    Leslie

  6. Patrick Roth [MSFT] says:

    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

     exec(@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.

  7. Eduardo Barbosa says:

    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

     begin

       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)

     end

     if @OutFullRecords=1

     begin

       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)

     end

    .

    .

    .

  8. Patrick Roth [MSFT] says:

    Thanks Eduardo!

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

  9. Eduardo Barbosa says:

    You're welcome, and thanks to you!

    I forgot to add the fix in table name with the option "OutFullRecords"

    .

    .

    .

     if @OutFullRecords=1

     begin

       set @sql='if exists (select * from ['+@tbl+'] (nolock) '

    .

    .

    .

  10. David Musgrave says:

    Thanks Eduardo

    I have updated the code to resolve these issues and format it nicer.

    David

  11. Victoria Yudin says:

    Thanks David,

    This is fabulous!!

    -Victoria

  12. Marc K says:

    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?

  13. David Musgrave says:

    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.

    David

  14. Adriaan Davel says:

    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

  15. David Musgrave says:

    Hi Adriaan

    Have a look at blogs.msdn.com/…/updated-spsearchonalldb-sql-stored-procedure-to-search-an-entire-database.aspx

    This follow up article has the Locate Value application which can be used without installing a Stored Procedure.

    David