在数据库所有表中搜索字符串

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