找出所有字段
1 select a.name as columnname,object_name(a.id)as tablename into t from syscolumns a,
2 sysobjects b,
3 systypes c
4 where a.id=b.id
5 and a.xtype=c.xtype
6 and b.xtype='u'
7 and c.name in('varchar','nvarchar','char','nchar','text','ntext')
8 and object_name(a.id)<>'t'
9
10 go
生成查询SQL的方法
12 create function udf_genSQL(@tableName varchar(1000),@keyword varchar(1000))
13 returns varchar(8000)
14 as
15 begin
16 declare @sql varchar(8000)
17 set @sql='select * from '+@tableName +' where 1=2 '
18 select @sql=@sql+' or '+
19 columnname +' like ''%'+@keyword+'%''' from t
20 where tablename=@tablename
21 return @sql
22 end
23 go
24
25
生成SQL
26 select dbo.udf_genSQL(tableName,'a') from t group by tablename
27
删除临时表
28 drop table t
29 drop function dbo.udf_genSQL
