spSearchOnAllDB: SQL Stored Procedure to Search an Entire Database

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.

spSearchOnAllDB.zip