From the Useful SQL Scripts Series.
This stored procedure 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 minor changes to make it more suitable for working in a Dynamics GP environment, such as granting of access to DYNGRP.
Note: To include a single quote (') character in the search string, you will need to replace the single quote (') with 4 single quotes in a row (''''). Doubling up a single quote will prevent the early termination of the search parameter and because the parameter is used in dynamically created scripts inside the stored procedure doubling up again allows the dynamic scripts to run correctly.
The script is available as an attachment at the bottom of this post.
David
24-Jun-2009: Added hint about searching for single quotes.
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.
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.