How do I write an SQL query which looks for a value in all columns and all tables in my database?

Question

I'm trying to look for a value in my Microsoft SQL Server 2008 database but I don't know what column or table to look in. I'm trying to craft a query which will just look in all tables and all columns for my value.

Answer 1

You probably could do it using dynamic sql using sys.cols & sys.tables you should be able to create the query.

This will, in all likelyhood, be an extremely long running query.

I rethought my answer and if you run the query below it will generate a number of sql statements, if you run those statements you will find out which column has the value you want. Just replace [your value here] with the appropriate value. This is assuming your value is a varchar.

SELECT 'SELECT ''' + TABLE_NAME + '.' + column_name + 
   ''' FROM ' + TABLE_NAME + ' WHERE ' + 
   column_name + ' = ''[your value here]'''
FROM INFORMATION_SCHEMA.COLUMNS 
WHERE DATA_TYPE = 'varchar';