统计数据库有该字段名的表

背景

在开发工作中，有时候要查询一下有哪些表使用到该字段名，例如电商平台查一下有什么表用到了订单号，好去评估改动范围等等

列出某库下所有表名

// 列出某库下所有表名
	TABLE_NAME 
FROM
	INFORMATION_SCHEMA.TABLES 
WHERE
	TABLE_TYPE = "BASE TABLE" 
	AND TABLE_SCHEMA = "库名"

列出有某字段名的所有表

// 列出有某字段名的所有表
SELECT
	TABLE_NAME 
FROM
	information_schema.COLUMNS 
WHERE
	table_name IN 
	( SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = "BASE TABLE" AND TABLE_SCHEMA = "库名" ) 
	AND column_name = '字段名'

列出没有某字段名的所有表

// 列出没有某字段名的所有表
SELECT
	q.TABLE_NAME 
FROM
	( SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = "BASE TABLE" AND TABLE_SCHEMA = "库名" ) q 
WHERE
	q.TABLE_NAME NOT IN (
	SELECT
		TABLE_NAME 
	FROM
		information_schema.COLUMNS 
	WHERE
		table_name IN ( SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = "BASE TABLE" AND TABLE_SCHEMA = "库名" ) 
	AND column_name = '字段名' 
	)