Searching a database for a value in any table, any field

One of my goals with this blog is to have a place for myself to go back and find useful queries that I either write or find on online.  This particular post is also a test of a code viewer plugin I’m trying on my WordPress.

The following code is helpful in the development process when you know of a particular value that is important for analytics, but you are unable to find the source of that value.  For example, you might have been given a report created a long, long time ago by a developer far, far away.  Your client wants you to create a newer, better, faster report showing cool things grouped by PartNumber.  The problem is that no field anywhere in your data is named PartNumber ( or any variation of PartNumber).  The good news is that the PartNumber values are distinctive.  You could use the following code to search every field, every table in your database to find all the places the specific PartNumber you see on the legacy report is stored.  That can be very helpful for finding the fields you need to focus on in … shall we say, a less than optimal database environment?

sample call:

Credit to the original author and a link to the site where I found this code can be found in the code block at the top of the stored procedure.

Note:  As the author points out in his blog post, this code is not super efficient when run against a database with a lot of data.  Try this link (Searching and finding a string value in all columns in a SQL Server table) for a couple variations on this theme from msSQLtips.com that are set up to search one table at a time (one option uses a cursor, the other does not).

Leave a Reply

Your email address will not be published. Required fields are marked *