spSearchOnAllDB: SQL Stored Procedure to Search an Entire Database

 

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.

The script is available as an attachment at the bottom of this post.

David

Posted: Monday, December 01, 2008 11:30 AM by David Musgrave

Filed under: Application, SQL

Attachment(s): spSearchOnAllDB.zip

Developing for Dynamics GP : spSearchOnAllDB: SQL Stored Procedure to Search an Entire Database