Search all columns in all the tables in a database for a specific value

How can I search all the columns in every table in my database for a specific value?

This question keeps popping up all the time. Here is a variation of this question discussed MSDN Forum T-SQL. Unfortunately there is no easy way to do this, you have to loop over all the tables in the database and search for the value.

As you can imagine if you have a huge database with a lot of tables this can take a really long time.

There are a couple of checks going on in this proc, if the data is not numeric then we skip all the numeric columns and do not search those columns. This code was created by our own gmmastros and what he did was create 3 different stored procedures, one for dates, numbers and strings. Then he created a 4th stored proc named FindMyData, this proc will only call the appropriate child procs if the datatype is right.

We only list the table name and the column name where the value is stored, we do not return that data!

First create the following 3 stored procedures

Just the date searching proc

vía Less Than Dot – Blog – The Ten Most Asked SQL Server Questions And Their Answers.