create proc p1 (@TableName varchar(100), @Scondition varchar(100))
as
declare c1 cursor for
select A.name from syscolumns A, sysobjects B
where A.id = B.id
and B.name = @TableName
declare @colname varchar(100)
open c1
fetch c1 into @colname
while @@fetch_status = 0
begin
exec ('select * from '+@TableName+' where cast('+@colname+' as varchar) like "%'+@Scondition+'%"'
fetch c1 into @colname
end
close c1
declare c1
as
declare c1 cursor for
select A.name from syscolumns A, sysobjects B
where A.id = B.id
and B.name = @TableName
declare @colname varchar(100)
open c1
fetch c1 into @colname
while @@fetch_status = 0
begin
exec ('select * from '+@TableName+' where cast('+@colname+' as varchar) like "%'+@Scondition+'%"'
fetch c1 into @colname
end
close c1
declare c1
as
declare c1 cursor for
select A.name from syscolumns A, sysobjects B
where A.id = B.id
and B.name = @TableName
declare @colname varchar(100)
open c1
fetch next from c1 into @colname
while @@fetch_status = 0
begin
exec ('select * from '+@TableName+' where cast('+@colname+' as varchar) like "%'+@Scondition+'%"')
fetch next from c1 into @colname
end
close c1
DEALLOCATE c1
declare @sql varchar(4000)
declare @col_name varchar(20)
declare @num int
declare @max_num int
select @max_num=max(colid) from syscolumns where id=object_id(@TableName)
set @num=0
set @sql=''
while(@num<@max_num)
begin
set @num=@num+1
select @col_name=name from syscolumns where id=object_id(@TableName) and colid=cast(@num as varchar)
set @sql=@sql+'Select * from '+@TableName+' where '+@col_name+' Like ''%'+@Scondition+'%'' union '
end
set @sql=left(@sql,len(@sql)-6)
print @sql
exec @sqlexec temp_proc '表名','条件'需要注意的是,你的字段必须是字符型,否则用like会出错的!