数据库级别查询

*注释 columnName 字段名 dbName 数据库名

1. 查询拥有某个字段的所有表

SELECT DISTINCT
    TABLE_NAME 
FROM
    information_schema.COLUMNS 
WHERE
    COLUMN_NAME = 'asset_code' 
    AND TABLE_SCHEMA = 'dbName' 
    AND TABLE_NAME NOT LIKE 'vw%';

如果要排除试图，在条件上再加：

AND TABLE_NAME NOT LIKE 'vw%'   注释：排除视图

2. 查询指定数据库所有的表名

SELECT
    table_name 
FROM
    information_schema.TABLES 
WHERE
    table_schema = 'dbName' 
    AND table_type = 'base table';

3. 查询数据库没有某字段的所有表

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%' 
    );