select * from ( select '表名', '字段名', '字段类型', '默认值', '是否可空', '注释', '主键' UNION ( SELECT a.TABLE_NAME '表名', a.COLUMN_NAME '字段名', a.COLUMN_TYPE '字段类型', a.COLUMN_DEFAULT '默认值', a.IS_NULLABLE '是否可空', a.COLUMN_COMMENT '注释', a.COLUMN_KEY '主键' FROM information_schema. COLUMNS a WHERE a.TABLE_SCHEMA = 'bbt' AND a.TABLE_NAME = 'tab_operation_data_collect' ORDER BY a.TABLE_NAME, a.ORDINAL_POSITION ) ) b; #查询字段在哪些表中存在 SELECT a.TABLE_NAME '表名', a.COLUMN_NAME '字段名', a.COLUMN_TYPE '字段类型', a.COLUMN_DEFAULT '默认值', a.IS_NULLABLE '是否可空', a.COLUMN_COMMENT '注释', a.COLUMN_KEY '主键' FROM information_schema. COLUMNS a WHERE a.TABLE_SCHEMA = 'bbt' -- AND a.TABLE_NAME = 't_gateway_log' and COLUMN_NAME like '%product_type%' -- and COLUMN_NAME like '%_policy_id%' ORDER BY a.TABLE_NAME, a.ORDINAL_POSITION; #查询表主键 SELECT DISTINCT table_name FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = 'batchjob' AND table_name NOT IN ( SELECT DISTINCT table_name FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = 'bbt' AND ( column_key = 'PRI' OR column_key = 'UNI' ) );