declare @key varchar(30)

set @key = '广州' --替换为要查找的字符串

DECLARE @tabName VARCHAR(40),@colName VARCHAR(40)

DECLARE @sql VARCHAR(2000)

declare @tsql varchar(8000)

DECLARE tabCursor CURSOR FOR

SELECT name from ccfs3.dbo.sysobjects WHERE xtype = 'u' AND name <> 'dtproperties'

OPEN tabCursor

FETCH NEXT from tabCursor INTO @tabName

WHILE @@fetch_status = 0

BEGIN

set @tsql = ''

DECLARE colCursor CURSOR FOR Select Name from SysColumns Where id=Object_Id(@tabName) --and xtype=167

OPEN colCursor

FETCH NEXT from colCursor INTO @colName

WHILE @@fetch_status = 0

BEGIN

SET @sql = 'if(exists(select * from ' + @tabName + ' where '

SET @sql = @sql + @colName + ' like ''%' + @key + '%'')) begin select * from '

set @sql = @sql + @tabName + ' where ' + @colName + ' like ''%' + @key + '%'';select '''

+ @tabName + ''' as TableName end'

set @tsql = @tsql + @sql + ';'

print @tsql

FETCH NEXT from colCursor INTO @colName

END

exec(@tsql)

CLOSE colCursor

DEALLOCATE colCursor

FETCH NEXT from tabCursor INTO @tabName

END

CLOSE tabCursor

DEALLOCATE tabCursor