[SqlServer] 全库搜索指定字符串

今天有同事问我要个脚本，给定一个字符串，在库里查出哪个表的哪个列的值包含它。写完之后，觉得自己将来可能也要用到，就暂且在这备个份。

if OBJECT_ID('tempdb..#t') is not null
	drop table tempdb..#t
GO
declare @s nvarchar(256) --指定字符串
set @s='爱'

declare @sql nvarchar(max)
select top 100 @sql=ISNULL(@sql+';','')+'insert #t select top 1 '''+tablename+''','''
	+columnname+''','+columnname+' from '+tablename+' where charindex('''+@s+''','+columnname+')>0'
from
(
	select a.name as tablename,b.name as columnname
	from sysobjects a
	join syscolumns b on a.id=b.id
	join systypes c on b.xtype=c.xtype
	where a.type='u'
	and c.name in('text','ntext','varchar','char','nvarchar','nchar')
) t

create table #t(tablename nvarchar(128),columnname nvarchar(128),columndata nvarchar(max));
--print @sql
exec(@sql)
select * from #t
GO