|
在SQL中，提供一个关键字,使得关键字去匹配表中所有字段，要实现这个功能的方法很多.比如很容易的做法:
1.如果你的字段全是字符型的化那就把他全加起来.然后like
(这样做其实是一种偷懒与变通的作法,对于特殊的数据查询出来会有问题)
Select *
From 表名
Where 字段1+字段2+字段3+..... like '%关键字%'
2.还可以拼接sql来完成,举个小例子如下:
--建立测试环境
create table table1
(
col1 varchar(10),
col2 varchar(10),
col3 varchar(10)
)
insert into table1
select 'a','b','c'
union
select 'a1','b1','c1'
union
select 'a2','b2','c2'
select * from table1
--拼接sql语句
declare @temp table
(
[id] int IDENTITY(1,1),
[Column_name] varchar(10)
)
declare @tempId int,@colName varchar(10),@sql varchar(500)
set @sql = 'select * from table1 where '
insert into @temp (Column_name)
SELECT dbo.syscolumns.name AS Column_name
FROM dbo.syscolumns INNER JOIN
dbo.sysobjects ON dbo.syscolumns.id = dbo.sysobjects.id
WHERE dbo.sysobjects.name='table1'and
(dbo.sysobjects.xtype = 'u') AND (NOT (dbo.sysobjects.name LIKE 'dtproperties'))
--select * from @temp
WHILE EXISTS(select [id] from @temp)
begin
SET ROWCOUNT 1
select @tempId = [id],@colName=[Column_name] from @temp
SET ROWCOUNT 0
delete from @temp where [id] = @tempId
set @sql = @sql+' '+@colName +' like ''%a%'' or'
end
set @sql = stuff(@sql,len(@sql)-1,2,'')
exec (@sql)
| 
