查找内容
if OBJECT_ID('temp_search_table') is not null
drop table temp_search_table
go
--存放找到的表、列、值
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 CONVERT(varchar(max),['+c.name +']) like '''+@search_str+''';'
from sys.VIEWS 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 sys.tab
--最后查找,你找到的内容
select *
from temp_search_table
查找关键字字段名
select c.name,t.name from sys.VIEWS 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
where upper(c.name) like '%CUST%'
or upper(c.name) like '%EMAIL%'