--我写了一个在整个数据库查找有该值的记录,你可以稍做修改 declare @s as varchar(500) -- 要查询的字符串 declare @t as varchar(500) -- 要查询的表名 declare @sql as varchar(8000) set @s = 'simba' set @t = '' --''为所有表 , -----------查询库中包含指定 指定字符串的 记录 create table #tmp_result ( tablename varchar(500) , columnname varchar(500) , recordcount int ) declare cur_sql cursor for select 'if exists ( select 0 from [' + o.name +'] where ['+c.name + '] like ''%' + @s + '%'' having count(1) > 0 ) begin insert #tmp_result select ''[' + o.name + ']'',''[' + c.name + ']'' ,count(1) as ct from [' + o.name +'] where ['+c.name + '] like ''%' + @s + '%'' having count(1) > 0 ; end ' from syscolumns c inner join sysobjects o on c.id = o.id inner join systypes t on c.xusertype = t.xusertype where o.xtype = 'u' and o.name like '%'+ @t+'%' and t.name in ('text' --,'sql_variant' ,'ntext' , 'varchar' ,'char' , 'nvarchar' , 'nchar' ,'xml') and c.length >= len(@s)
OPEN cur_sqlFETCH NEXT FROM cur_sql INTO @sql WHILE @@FETCH_STATUS = 0 BEGIN exec (@sql) FETCH NEXT FROM cur_sql INTO @sql END CLOSE cur_sql DEALLOCATE cur_sqlselect * from #tmp_result drop table #tmp_result
不用or就用union all
1、首先执行sp_help your_table;把列名全部copy出来,放到记事本也行,不过最后放到ultraedit(支持列编辑)
然后每行的开头填入:name like '%
行尾填入:%'
在最前面加上where,搞定!
1) 将表名和 关键字做参数
2)在系统中找出 表参数 所有的列
3)构选一个varchar串, 构成成最终要执行的SQL语句
4)动态执行该串;
----------------------
每次查询时,只要执行该存储过程就可以了!
declare @s as varchar(500) -- 要查询的字符串
declare @t as varchar(500) -- 要查询的表名
declare @sql as varchar(8000) set @s = 'simba'
set @t = '' --''为所有表 ,
-----------查询库中包含指定 指定字符串的 记录
create table #tmp_result
(
tablename varchar(500) ,
columnname varchar(500) ,
recordcount int
)
declare cur_sql cursor for
select
'if exists
( select 0 from [' + o.name +'] where ['+c.name + '] like ''%' + @s + '%'' having count(1) > 0 )
begin
insert #tmp_result
select ''[' + o.name + ']'',''[' + c.name + ']'' ,count(1) as ct from [' + o.name +']
where ['+c.name + '] like ''%' + @s + '%'' having count(1) > 0 ;
end '
from
syscolumns c
inner join
sysobjects o on c.id = o.id
inner join
systypes t on c.xusertype = t.xusertype
where o.xtype = 'u'
and o.name like '%'+ @t+'%'
and t.name in ('text'
--,'sql_variant'
,'ntext' , 'varchar' ,'char' , 'nvarchar' , 'nchar' ,'xml')
and c.length >= len(@s)
OPEN cur_sqlFETCH NEXT FROM cur_sql INTO @sql
WHILE @@FETCH_STATUS = 0
BEGIN
exec (@sql)
FETCH NEXT FROM cur_sql INTO @sql
END
CLOSE cur_sql
DEALLOCATE cur_sqlselect * from #tmp_result
drop table #tmp_result