SQLserver 查看数据库包含指点数据的表（字段）

 找出所有字段

 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