查询数据库包含某个字段的所有表

背景：最近，因为项目重构，需要统计老项目的数据库表中包含业务属性的某个字段，涉及到有哪些表；

​ 所以，总结如下：

一、查询指定库拥有某字段的表

-- 注释： columnName 字段名   dbName 数据库名
-- AND TABLE_NAME NOT LIKE 'vw%'   --> 作用：排除视图
SELECT DISTINCT TABLE_NAME FROM information_schema.COLUMNS WHERE COLUMN_NAME = 'columnName' AND TABLE_SCHEMA='dbName' AND TABLE_NAME NOT LIKE 'vw%';

-- 举例：我要查数据库[fengchen]中包含字段[id]的所有表
select distinct TABLE_NAME from information_schema.`COLUMNS` where COLUMN_NAME='id' and TABLE_SCHEMA='fengchen' and TABLE_NAME not like 'vm%';

二、查询指定数据库所有的表名

-- 通用模板
select table_name from information_schema.tables where table_schema='dbName' and table_type='base table';

-- 例如：我要查询数据库【fengchen】中的所有表
select table_name from information_schema.`TABLES` where TABLE_SCHEMA='fengchen' and TABLE_TYPE='base table';

三、查询指定数据库没有某字段的所有表

-- 通用模板
SELECT
	table_name 
FROM
	information_schema.TABLES 
WHERE
	table_schema = 'dbName' 
	AND table_type = 'base table' 
	AND TABLE_NAME NOT IN ( SELECT DISTINCT TABLE_NAME FROM information_schema.COLUMNS WHERE COLUMN_NAME = 'culumnName' AND TABLE_SCHEMA = 'dbName' AND TABLE_NAME NOT LIKE 'vw%' );


-- 例如：查询数据库【fengchen】中表里没有字段【id】的所有表
SELECT
	table_name 
FROM
	information_schema.`TABLES` 
WHERE
	TABLE_SCHEMA = 'fengchen' 
	AND TABLE_TYPE = 'base table' 
	AND TABLE_NAME NOT IN ( SELECT DISTINCT TABLE_NAME FROM information_schema.`COLUMNS` WHERE COLUMN_NAME = 'id' AND TABLE_SCHEMA = 'fengchen' AND TABLE_NAME NOT LIKE 'vm' )