数据库中查找某值所在的表、字段（Oracle、SqlServer）

缺乏、安全感 2022-05-23 12:16 343阅读 0赞

## 1. 基本思路 ##

不管什么数据库，基本思路都相同。  
首先通过一些记录数据表、列等基本信息的**系统表**查找出所有的**表名、列名**。  
然后遍历所有这些表和列，拼接类似`select * from 表名 where 列名 like @要查找值`的形式去查找。

## 2. Oracle中查找某值所在的表、字段 ##

DECLARE  
    CURSOR cur_query IS  
      SELECT table_name, column_name, data_type FROM user_tab_columns;  
      a NUMBER;  
      sql_hard VARCHAR2(2000);  
      vv NUMBER;  
    BEGIN  
      DBMS_OUTPUT.ENABLE(buffer_size => null);
      FOR rec1 IN cur_query LOOP  
      a:=0;  
      IF rec1.data_type ='VARCHAR2' THEN  
      a := 1;  
      END IF;  
      IF a>0 THEN  
      sql_hard := '';  
      sql_hard := 'SELECT COUNT(*) FROM '|| rec1.table_name ||' WHERE '  ||rec1.column_name  || '=''管理员''';
      dbms_output.put_line(sql_hard);   
      EXECUTE IMMEDIATE sql_hard INTO vv;  
      IF vv > 0 THEN  
       dbms_output.put_line('[字段值所在的表.字段]:['||rec1.table_name||'].['||rec1.column_name||']');  
      END IF;   
      END IF;  
      END LOOP;  
    END;

直接在PL/SQL的sql窗口中执行，在输出中即可看到查找结果  
![这里写图片描述][70]

DECLARE  
    CURSOR cur_query IS   
      SELECT table_name, column_name, data_type FROM user_tab_columns;   
      a NUMBER;   
      sql_hard VARCHAR2(2000);   
      vv NUMBER;  
    BEGIN   
      DBMS_OUTPUT.ENABLE(buffer_size => null);
      FOR rec1 IN cur_query LOOP  
      a:=0;   
      IF rec1.data_type ='VARCHAR2' OR rec1.data_type='CHAR' THEN   
      a := 1;   
      END IF;   
      IF a>0 THEN   
      sql_hard := '';   
      sql_hard := 'SELECT count(*) FROM '|| rec1.table_name ||' where '   
      ||rec1.column_name|| ' like''管理员''';
      dbms_output.put_line(sql_hard);   
      EXECUTE IMMEDIATE sql_hard INTO vv;  
      IF vv > 0 THEN dbms_output.put_line('[字段值所在的表.字段]:['||rec1.table_name||'].['||rec1.column_name||']');   
      END IF;  
      END IF;  
      END LOOP;  
    END;

## 3. SqlServer中查找某值所在的表、字段 ##

CREATE PROCEDURE [dbo].[SP_FindValueInDB]
    (
        @value VARCHAR(1024)
    ) 
    AS
    BEGIN
        SET NOCOUNT ON;
        DECLARE @sql VARCHAR(1024) 
        DECLARE @table VARCHAR(64) 
        DECLARE @column VARCHAR(64) 
        CREATE TABLE #t ( 
            tablename VARCHAR(64), 
            columnname VARCHAR(64) 
        ) 
        DECLARE TABLES CURSOR FOR 
        SELECT o.name, c.name FROM syscolumns c 
        INNER JOIN sysobjects o ON c.id = o.id 
        WHERE o.type = 'U' AND c.xtype IN (167, 175, 231, 239) 
        ORDER BY o.name, c.name 
        OPEN TABLES 
            FETCH NEXT FROM TABLES 
            INTO @table, @column 
            WHILE @@FETCH_STATUS = 0 
            BEGIN 
                SET @sql = 'IF EXISTS(SELECT NULL FROM [' + @table + '] ' 
                SET @sql = @sql + 'WHERE RTRIM(LTRIM([' + @column + '])) LIKE ''%' + @value + '%'') ' SET @sql = @sql + 'INSERT INTO #t VALUES (''' + @table + ''', ''' 
                SET @sql = @sql + @column + ''')' 
                EXEC(@sql) 
                FETCH NEXT FROM TABLES 
                INTO @table, @column 
            END 
        CLOSE TABLES 
        DEALLOCATE TABLES 
        SELECT * FROM #t 
        DROP TABLE #t 
    End

使用方法：

exec [SP_FindValueInDB]  '测试'

[70]: /images/20220523/6368b4a50e164c21b9257275e74ccfcb.png