How to search a Microsoft SQL database for a data value
Microsoft SQL is not able to inherently search for data values across all tables and columns in a database. For SQL Server 2008 and higher, there is a built-in method to do this, but it is generally insufficient. A SQL script is therefore needed to do this.
This article includes two SQL scripts that will search a specified database's tables and columns for a data value. One is a script that is ran manually after entering the value to search for, the other is a stored procedure that can be installed into the database to use for convenience. Each script contains instructions on how to use them included in the script code. Note: These scripts are provided "AS-IS", with no guarantees that they will meet the customer's needs. These should be evaulated before ran if there are any concerns about using them.
The attached scripts differ from most other search scripts by including additional useful features. These are documented below.
Other Search Scripts
Attached Search Scripts
Warnings: (1) Running these scripts on a production server during business hours may impact performance temporarily during execution. (2) Please test these script in a test environment before trying them on a production server to ensure they will meet your needs. (3) As necessary, ensure these scripts are approved by your DBA before use. (4) For some environments, installation and use of a stored procedure cannot be performed due to security, protocols, concerns or other reasons. If so, use the normal script to manually perform searches instead.
Find all tables that contain a specific GUID. See KB 171823