-- 查询整个数据库中某个特定值所在的表和字段的方法

# flush tables;

-- 创建表来存储查询结果

drop table if exists tmp_table;

CREATE TABLE tmp_table (

  tablename   VARCHAR(1024) null,

  columnname  VARCHAR(1024) null,

  columnvalue VARCHAR(1024) null

);

DROP PROCEDURE IF EXISTS search_value;

DELIMITER $$

-- v1内容值

CREATE PROCEDURE search_value(v1 VARCHAR(1024))

  BEGIN

    DECLARE done INT DEFAULT 0;

    DECLARE m_table VARCHAR(64);

    DECLARE m_column VARCHAR(64);

    -- 查询数据库字段类型为'varchar' 的字段

    DECLARE m_tables CURSOR

    FOR

      select table_name, column_name

      from information_schema.columns

      where data_type = 'varchar'

      -- 注意修改这里的 table_schema

      and table_schema = 'table_schema'

    --      and table_name = 'biz_patient_register'

    ;

    declare continue handler for not FOUND set done = 1;

    set @_v = v1;

    open m_tables;

    FETCH m_tables

    INTO m_table, m_column;

    WHILE done != 1 do

      #       insert into tmp_table select m_table as tablename, m_column as columnname, v1 as columnvalue;

      set @m_sql = concat('insert into tmp_table select ''', m_table, ''' as tablename,''', m_column,

                          ''' as columnname,`', m_column, '` as columnvalue from `', m_table, '` where `', m_column,

                          '` = ''%', v1, '%'';');

      -- 编译sql

      prepare stmt from @m_sql;

      -- 执行sqL

      EXECUTE stmt;

      deallocate prepare stmt;

      #     select m_table, m_column;

      FETCH m_tables

      INTO m_table, m_column;

    END WHILE;

    CLOSE m_tables;

  End $$

DELIMITER ;

-- 存储过程创建完成

call search_value('152'); -- 执行存储过程

select *

from tmp_table; -- 查询存储过程执行的结果