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

/*
    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