统计数据库 所有表有没有指定字段

SELECT
	ist.TABLE_NAME,
	ist.table_comment,
	missing_column1,
	missing_column2,
	missing_column3,
	missing_column4,
	missing_column5
FROM
	information_schema.TABLES ist
	-- sys_org_code
	LEFT JOIN (
	SELECT
		TABLE_NAME,
		'sys_org_code' AS missing_column1
	FROM
		information_schema.TABLES 
	WHERE
		TABLE_SCHEMA = 'gas_test' -- 替换为你的数据库名
		
		AND TABLE_NAME NOT IN ( SELECT TABLE_NAME FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = 'gas_test' -- 替换为你的数据库名
			AND COLUMN_NAME = 'sys_org_code' -- 替换为你要排除的字段名
		)) soc ON soc.TABLE_NAME = ist.TABLE_NAME
	-- create_by
	LEFT JOIN (
	SELECT
		TABLE_NAME,
		'create_by' AS missing_column2
	FROM
		information_schema.TABLES 
	WHERE
		TABLE_SCHEMA = 'gas_test' -- 替换为你的数据库名
		
		AND TABLE_NAME NOT IN ( SELECT TABLE_NAME FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = 'gas_test' -- 替换为你的数据库名
			AND COLUMN_NAME = 'create_by' -- 替换为你要排除的字段名
		)) cb ON cb.TABLE_NAME = ist.TABLE_NAME
	-- create_time
	LEFT JOIN (
	SELECT
		TABLE_NAME,
		'create_time' AS missing_column3
	FROM
		information_schema.TABLES 
	WHERE
		TABLE_SCHEMA = 'gas_test' -- 替换为你的数据库名
		
		AND TABLE_NAME NOT IN ( SELECT TABLE_NAME FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = 'gas_test' -- 替换为你的数据库名
			AND COLUMN_NAME = 'create_time' -- 替换为你要排除的字段名
		)) ct ON ct.TABLE_NAME = ist.TABLE_NAME
	-- update_by
	LEFT JOIN (
	SELECT
		TABLE_NAME,
		'update_by' AS missing_column4
	FROM
		information_schema.TABLES 
	WHERE
		TABLE_SCHEMA = 'gas_test' -- 替换为你的数据库名
		
		AND TABLE_NAME NOT IN ( SELECT TABLE_NAME FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = 'gas_test' -- 替换为你的数据库名
			AND COLUMN_NAME = 'update_by' -- 替换为你要排除的字段名
		)) ub ON ub.TABLE_NAME = ist.TABLE_NAME
	-- update_time
	LEFT JOIN (
	SELECT
		TABLE_NAME,
		'update_time' AS missing_column5
	FROM
		information_schema.TABLES 
	WHERE
		TABLE_SCHEMA = 'gas_test' -- 替换为你的数据库名
		
		AND TABLE_NAME NOT IN ( SELECT TABLE_NAME FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = 'gas_test' -- 替换为你的数据库名
			AND COLUMN_NAME = 'update_time' -- 替换为你要排除的字段名
		)) ut ON ut.TABLE_NAME = ist.TABLE_NAME 
WHERE
	ist.table_schema = 'gas_test' -- 替换为你的数据库名