在当前数据库的所有表，所有字段查找字符串

/*
    search a string in all databaes tables and all fields
*/

if object_id( 'search_db') is not null
    drop proc search_db
go

create proc search_db
    @ table nvarchar( 100),
    @cond nvarchar( 512)
as
declare hCForEach cursor global for
select sqlstmt =
'if exists (' + stmt + ')
    print ''' + replace(stmt, '''', '''''') + ''''
from(
    select stmt= 'select * from [' + TABLE_NAME + '] where convert(nvarchar, [' + COLUMN_NAME + ']) like ''' + @cond + ''''
    from INFORMATION_SCHEMA.COLUMNS A
    where (IsNull(@ table, '') = '' or TABLE_NAME like @ table) and DATA_TYPE <> 'image' and
        ( SELECT TABLE_TYPE from INFORMATION_SCHEMA.TABLES B where A.TABLE_NAME = B.TABLE_NAME) = 'BASE TABLE'
) T

exec sp_msforeach_worker @command1 = N '?'
go

exec search_db '', '%HKG%'

drop proc search_db