Updated: spSearchOnAllDB: SQL Stored Procedure to Search an Entire Database
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:
- It now handles tables and columns with long names which can include spaces.
- 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.