查询数据库包含某个字符串的表名和该条记录_doris查看名称中包含某个字符串的表-优快云博客

本文介绍了一个使用T-SQL编写的动态查询生成器存储过程，该过程可以根据指定的关键字搜索所有表中的匹配项，并返回相应的表名和数据。此过程通过游标遍历数据库中的所有表及字段来实现。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >


------if exists (select * from sysobjects where name= 'updMoney' and xtype ='P')
if(OBJECT_ID('aaa','p') is not null)
drop proc aaa
go 
create proc aaa 
as 
DECLARE @key VARCHAR(30)  
SET @key = '办公室'    --替换为要查找的字符串  
DECLARE @tabName VARCHAR(40),@colName VARCHAR(40)  
DECLARE @sql VARCHAR(2000)  
DECLARE @tsql VARCHAR(8000)  
  
DECLARE tabCursor CURSOR FOR  
SELECT name FROM sysobjects WHERE xtype = 'u' AND name <> 'dtproperties'  
OPEN tabCursor  
FETCH NEXT FROM tabCursor INTO @tabName  
WHILE @@fetch_status = 0  
BEGIN  
    SET @tsql = ''  
    DECLARE colCursor CURSOR FOR   
    SELECT Name FROM SysColumns WHERE id=Object_Id(@tabName) 
    OPEN colCursor  
    FETCH NEXT FROM colCursor INTO @colName  
    WHILE @@fetch_status = 0  
    BEGIN  
        SET @sql = 'if(exists(select * from ' + @tabName + ' where '  
        SET @sql = @sql + @colName + ' like ''%' + @key + '%'')) begin  select * from '  
        SET @sql = @sql + @tabName + ' where ' + @colName + ' like ''%' + @key + '%'';select '''  
        + @tabName + ''' as TableName  end'  
        SET @tsql = @tsql + @sql + ';'  
          
        FETCH NEXT FROM colCursor INTO @colName  
    END  
    EXEC(@tsql) 
    print @tsql
    CLOSE colCursor  
    DEALLOCATE colCursor  
      
    FETCH NEXT FROM tabCursor INTO @tabName  
END  
CLOSE tabCursor  
DEALLOCATE tabCursor  
 
go
 
exec aaa