SpookZanG

从系统表自动生成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