Developing for Dynamics GP

by David Musgrave (Perth, WA, Australia) and the Microsoft Dynamics GP Developer Support Team (Fargo, ND, USA)

spSearchOnAllDB: SQL Stored Procedure to Search an Entire Database

spSearchOnAllDB: SQL Stored Procedure to Search an Entire Database

  • Comments 18

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.

Attachment: spSearchOnAllDB.zip
  • From Vaidy Mohan's Blog

    http://vmdyngp.blogspot.com/2008/12/sql-series-from-david-musgrave-team.html

  • Posting from Dynamics GP Blogster

    http://dynamicsgpblogster.blogspot.com/2008/12/sql-mania-series-at-developing-for.html

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

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

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

  • Thank you David, very good script.

    Hussain Al-Yousef

    hyou91@gmail.com

  • 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

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

  • 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

    .

    .

    .

  • Thanks Eduardo!

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

  • 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) '

    .

    .

    .

  • Thanks Eduardo

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

    David

  • Thanks David,

    This is fabulous!!

    -Victoria

  • 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?

  • 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

Page 1 of 2 (18 items) 12
Comments Information

PLEASE READ BEFORE POSTING

Please only post comments relating to the topic of this page.

If you wish to ask a technical question, please use the links in the links section (scroll down, on right hand side) to ask on the Newsgroups or Forums. If you ask on the Newsgroups or Forums, others in the community can respond and the answers are available for everyone in the future.

Leave a Comment
  • Please add 6 and 2 and type the answer here:
  • Post