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

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执行历史记录：

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操作返回历史记录：

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行

smartsofthelp 最好的数据库开发软件

下载地址：https://pan.baidu.com/s/1RzJq5rKEJWq5xGUIo7jYjw?pwd=8888

下载https://pan.baidu.com/s/1RzJq5rKEJWq5xGUIo7jYjw?pwd=8888