Search a Microsoft SQL database for a data value

Products

• IT Management Suite
• Asset Management Solution

Issue/Introduction

 How to search a Microsoft SQL database for a data value

Resolution

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
 

• Only returns the table and column of a match but not the results.
• If more than one match is found, this is not noted, as only the table and column are displayed.
• There are limitations on the data size that can be used.
• Only some text data types are searched, in many cases just one type. This prevents searching for GUIDs, numbers and dates.
• Limited on just searching for the data value, no other parameters for search criteria.
   

Attached Search Scripts
 

• Includes both standard and stored procedure variations. It's up to the user's discretion for which script to use as they otherwise do exactly the same thing. Notes: (1) These scripts make no changes to the database's data. (2) These scripts are not designed for any specific database but can be used on any Microsoft SQL Server 2005 and above database, such as the Symantec_CMDB database used by the Symantec Management Platform. (3) The stored procedure, "spSearchForData.sql", when saved to the database, does not make any changes to existing stored procedures. This is not ran on any schedule and is only ran when the user manually invokes it to perform a search. There is therefore zero impact that this will cause for performance or operations of a database by having the script installed. (Running it to search for data may slow the server down while it processes, however, as noted below in the Warnings section.)
• Returns the table, column, data type and size (if text) and results.
• No limitations on size or data type.
• Can use exact match or case sensitivity.
   

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.

Related Article

Find all tables that contain a specific GUID. See KB 171823