Developing for Dynamics GP

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

Updated: spSearchOnAllDB: SQL Stored Procedure to Search an Entire Database

Updated: spSearchOnAllDB: SQL Stored Procedure to Search an Entire Database

Rate This
  • Comments 2

David Meego - Click for blog homepageFrom the Useful SQL Scripts Series.

Back in December 2008, I posted an article providing the spSearchOnAllDB stored procedure, originally created by Oufimtsev Gleb.

Based on feedback in the comments of that article, I have made two lots of updates to the script to fix some issues:

  1. It now handles tables and columns with long names which can include spaces.
     
  2. It now automatically doubles up any single quote characters in the search string passed to the stored procedure to avoid early termination of the strings used for the dynamically created code.

 

Note: To pass a single quote (') into the stored procedure, you will need to double it up to 2 single quotes ('') when you type in the parameter.

For example: to search for any single quotes in the data use: exec spSearchOnAllDB '%''%'

 

The updated script is provided at the bottom of the original post:

 

Also check out the Locate Value application attached to the bottom of this post which performs a similar function without a stored procedure.

Enjoy 

David

14-Aug-2013: Added Locate Value application to bottom of article. This application can perform the same function as spSearchOnAllDB.

Attachment: LocVal.zip
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 4 and 8 and type the answer here:
  • Post