一个表里面字段数目不定,可能10个或者20,30更多.
现在想查询出任意一字段有包含 "电" 这个字的记录.如果字段数目确定的话,
我可以这样写select * from 表 where 字段1 like '%电%' or 字段2 like '%电%' or 字段3 like '%电%'但因为字段数目不定,就不行了,请大侠给个好方法.
现在想查询出任意一字段有包含 "电" 这个字的记录.如果字段数目确定的话,
我可以这样写select * from 表 where 字段1 like '%电%' or 字段2 like '%电%' or 字段3 like '%电%'但因为字段数目不定,就不行了,请大侠给个好方法.
-- sql列出表所有字段
select name from syscolumns where id=object_id('sysct')
-----------------------------------------------------
DECLARE @table_name varchar(200),@col_name varchar(200)declare tutest_1 cursor forselect a.name, c.name from sysobjects a
join sysindexes b on a.id=b.id
right join syscolumns c on c.id=b.id
--求字段的类型
join systypes d on d.xusertype=c.xusertype
where b.indid in (0,1)
and a.name='sysct' -- 不加这个表名,即为查找所有表中字段包含'电'的记录
and a.xtype='U'
and b.rows>100
and d.name like'%char%'OPEN tutest_1FETCH NEXT FROM tutest_1
INTO @table_name, @col_nameWHILE @@FETCH_STATUS = 0
begin
declare @sql varchar(8000)set @sql=''set @sql=+@sql+ 'select * from '+@table_name +' where '+ @col_name +' like ''%电%'''--exec(@sql)
-- 也可以把语句打出来
if @@rowcount>0
print(@sql)FETCH NEXT FROM tutest_1 INTO @table_name, @col_name
end
CLOSE tutest_1
DEALLOCATE tutest_1
set @sql=''
select @sql=@sql+'['+name+']'+' like ''%1%'' or ' from syscolumns where id=object_id('test')
order by colidset @sql=stuff(@sql,len(@sql)-1,2,'')
print @sql
exec('select * from test where '+@sql)test为表名
like 后的1改为你需要的