spSearchOnAllDB: SQL Stored Procedure to Search an Entire Database
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.
