SQL如何遍历所有表的一个字段来查询数据

create table temp_search_table
(
table_name nvarchar(100),
column_name nvarchar(100),
column_search_value nvarchar(max)
)
go

declare @sql nvarchar(max);
declare @search_str nvarchar(100);

set @sql = ''
set @search_str = '陈召海'; --你要找的值

select @sql = @sql + 'insert into temp_search_table '+
'select '''+t.name +''' as table_name,''' +
c.name+ ''' as column_name, ['+
c.name + '] from ['+t.name +
'] where ['+c.name +'] like '''+@search_str+''';'

from sys.tables t
inner join sys.columns c
on t.object_id = c.object_id
inner join sys.types tp
on c.system_type_id = tp.system_type_id
and c.user_type_id = tp.user_type_id
and tp.name in ('char','varchar','nchar','nvarchar')
where t.name <> 'temp_search_table'

--select @sql

exec(@sql)


--最后查找,你找到的内容

select * from temp_search_table