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

 找出所有字段

 1 select a.name as columnname,object_name(a.id)as tablename into t from syscolumns a,
sysobjects b,
systypes c
where a.id=b.id  
and a.xtype=c.xtype
and b.xtype='u'
and c.name in('varchar','nvarchar','char','nchar','text','ntext')
and object_name(a.id)<>'t'
 
go


生成查询SQL的方法

create function udf_genSQL(@tableName varchar(1000),@keyword varchar(1000))
returns varchar(8000)
as
begin
    declare @sql varchar(8000)
    set @sql='select * from '+@tableName +' where 1=2 '
    select @sql=@sql+' or '+
       columnname +' like ''%'+@keyword+'%''' from t
    where tablename=@tablename
    return @sql
end
go

 

生成SQL

select dbo.udf_genSQL(tableName,'a') from t group by tablename
 

删除临时表

drop table t
drop function dbo.udf_genSQL