Updated: 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.

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.

LocVal.zip

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

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

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.