SQL 遍历查找方法摘录

 

查找内容

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%'