各种数据库中包含某个值的所表和字段的语句，支持Oracle、MySQL、SQLServer

前言

在项目开发工作中，可能会遇到如敏感数据清理或者分析其他系统数据库数据结构的情况。这样就会用到通过某个特定的值去反向查询数据库，分析出该值在该数据库中的表和字段，以此为切入点了解该数据库的基本结构。这里整理了各个类型的数据库（Oracle、MySQL、SQLServer）中具体执行方法。

---

一、整体思路

解决思路其实各个数据库都基本类似。

首先通过特定数据库系统表中查找到数据库表名和字段名。然后遍历所有的表和字段并拼接出类似SELECT count(*) FROM 表名 WHERE 字段名 LIKE '%要查找的特定值%'的形式去查找，若条数大于0则打印出表名和字段名。

二、各个数据库查询方法

1.MySQL

代码如下：

DROP TABLE  if EXISTS temp;
CREATE TABLE temp(tablename VARCHAR(255),LieName VARCHAR(255));

delimiter $$
DROP procedure IF EXISTS getDataByDbName $$
CREATE procedure getDataByDbName(in dbName VARCHAR(255),IN ziduan VARCHAR(255))
BEGIN
DECLARE num INT;
SET @STMT =CONCAT("SELECT COUNT(*) FROM ",dbName," WHERE `",ziduan,"` LIKE BINARY '%特定值%' INTO @num;");
PREPARE STMT FROM @STMT;
EXECUTE STMT;
IF(@num>0) THEN
    INSERT INTO temp VALUES (dbName,ziduan);
END IF;
end$$
delimiter ;

delimiter $$
DROP PROCEDURE IF EXISTS processquanjusou$$
CREATE PROCEDURE processquanjusou()
BEGIN
DECLARE done BOOLEAN DEFAULT 0;
DECLARE biao VARCHAR (255);
DECLARE ziduan VARCHAR (255);
DECLARE indexss CURSOR
FOR
SELECT TABLE_NAME,COLUMN_NAME FROM information_schema.COLUMNS WHERE TABLE_SCHEMA='数据库名' ;
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done=1;
OPEN indexss;
repeat
FETCH indexss INTO biao,ziduan;
call  getDataByDbName(biao,ziduan);
UNTIL done END repeat;
CLOSE indexss;
end$$
delimiter ;

CALL processquanjusou();

SELECT * FROM temp;

2.Oracle

代码如下：

DECLARE
CURSOR cur_query IS
  SELECT table_name, column_name, data_type FROM user_tab_columns;
  condition NUMBER;
  sql_count VARCHAR2(2000);
  counts NUMBER;
BEGIN
  DBMS_OUTPUT.ENABLE(buffer_size => null);
  FOR rec1 IN cur_query LOOP
    condition:=0;
    IF rec1.data_type ='VARCHAR2' OR rec1.data_type='CHAR' THEN
      condition:=1;
    END IF;
    IF condition>0 THEN
      sql_count := '';
      sql_count := 'SELECT count(*) FROM "'||rec1.table_name||'" WHERE "'||rec1.column_name||'" LIKE ''我要上热搜%''';
      EXECUTE IMMEDIATE sql_count INTO counts;
      IF counts > 0 THEN 
        dbms_output.put_line('SELECT * FROM "'||rec1.table_name||'" WHERE "'||rec1.column_name||'" LIKE ''我要上热搜%''');
      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]  '我要上热搜'

---

总结

提示：这里对文章进行总结：
例如：以上就是今天要讲的内容，本文仅仅简单介绍了pandas的使用，而pandas提供了大量能使我们快速便捷地处理数据的函数和方法。