通过列名查找表，通过字段值查找所在表数据库列名

一.通过字段值查找表(需要提供字段值和数据库的名称)

注意！！！会生成一张temp1的表，如果有会覆盖原来的表

DROP TABLE  if EXISTS temp1;
CREATE TABLE temp1(tablename VARCHAR(255),LieName VARCHAR(255));
 
# 将结束符改为//
delimiter //
DROP procedure IF EXISTS getDataByDbName//
# 两个必须的参数：数据库名dbName，字段名ziduan
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 searchAll//
CREATE PROCEDURE searchAll()
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='数据库名称' ;
/*02000代表异常：
1.SELECT INTO语句或INSERT语句的子查询的结果为空表。
2.在搜索的UPDATE或DELETE语句内标识的行数为零。
3.在FETCH语句中引用的游标位置处于结果表最后一行之后。 */
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 searchAll();
 
SELECT * FROM temp;

二.通过列名查找表(需要提供列名)

SELECT table_name
FROM information_schema.columns
WHERE column_name = '查找的字段' ;