SQLServer查询所有表所有字段包含xx的信息

从系统表自动生成sql语句来运行得到结果

 

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
 
 
 
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=1 '
 
    SELECT @sql=@sql+' or '+
 
       columnname +' like ''%'+@keyword+'%''' FROM t
 
    WHERE tablename=@tablename
 
    RETURN @sql
 
end
 
go
 
 
 
SELECT dbo.udf_genSQL(tableName,'a') FROM t GROUP BY tablename
 
 
 
DROP TABLE t
 
DROP FUNCTION dbo.udf_genSQL