Find which table has that column data

Question

I know how to find which table has that column name, by running:

select * From INFORMATION_SCHEMA.COLUMNS Where column_name = 'column value'

What i need now, is to find which tables have that certain column data. It doesn't matter which column it belongs, I can find it, i just don't know which table to look at.

Joining these tables is not a solution, since there are a lot of tables.

Pls. let me know if you have ideas. Thanks.

Answer 1

Will this do the job for you?

declare @data varchar(50)
    ,@sql varchar(max)
select @data = '%test%'

create table #Temp ([Table] varchar(200), [Column] varchar(200), [Data] varchar(max))

select @sql = isnull(@sql, '') + 'insert into #Temp select ''' + sys.tables.name + ''', ''' + sys.columns.name + ''', ' + sys.columns.name + ' from [' + sys.tables.name + '] where [' + sys.columns.name + '] like ''' + @data + ''';'
from sys.tables
inner join sys.columns
    on sys.columns.object_id = sys.tables.object_id

exec(@sql)

select * from #Temp order by [Table], [Column]
drop table #Temp