SQL实现全库搜索关键字所在表位置

最新推荐文章于 2023-10-07 16:24:04 发布

SmartSoftHelp开发辅助优化

最新推荐文章于 2023-10-07 16:24:04 发布

阅读量611

点赞数 1

分类专栏：数据库优化文章标签：性能优化 Powered by 金山文档

本文链接：https://blog.csdn.net/qq512929249/article/details/129231767

版权

数据库优化专栏收录该内容

35 篇文章 0 订阅

订阅专栏

if OBJECT_ID('tempdb..#t') is not null drop table tempdb..#t ; declare @s nvarchar(256) set @s='123' 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)); exec(@sql) select tablename as 表名称,columnname as 列名称,columndata as 数据列 from #t GO

SQL执行历史记录：

SQL操作返回历史记录：

2023-02-26 21:10:51:558 输入：if OBJECT_ID('tempdb..#t') is not null drop table tempdb..#t ; declare @s nvarchar(256) set @s='123' 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)); exec(@sql) select tablename as 表名称,columnname as 列名称,columndata as 数据列 from #t GO

2023-02-26 21:10:51:558 输出：全库关键字搜索： 19行