mysql，postgresql，oracle,hive元数据库等查看表结构信息

        我们在查询表结构的时候，比如mysql ，从information_schema里面获取，单独语句只能查询部分信息，比如：information_schema.tables 和information_schema.columns 。前者只能获取表相关数据，无法显示字段详信息，后者只能获取字段详细信息，无法获取表以及库的信息。

现在将两者结合在一起，获取需要的全部信息

本脚本目前针对mysql、oracle、hive和pg库，其他数据库后续会有补充。

本脚本可以快速查询你想要的整个数据库的表结构，如图：

mysql:

注意：脚本需要在information_schema下面运行

select *  from (
select *  from (
select a.TABLE_SCHEMA,a.TABLE_NAME,b.TABLE_COMMENT,a. COLUMN_NAME,a.COLUMN_COMMENT,a.COLUMN_TYPE,b.TABLE_ROWS,b.table_type from 
    (SELECT TABLE_SCHEMA,TABLE_NAME,COLUMN_NAME,COLUMN_COMMENT,COLUMN_TYPE FROM information_schema.`COLUMNS`  )  a left join 
    (SELECT TABLE_SCHEMA,TABLE_NAME,TABLE_COMMENT,TABLE_ROWS,table_type FROM information_schema.`TABLES` ) b
    on   a.TABLE_NAME=b.TABLE_NAME and a.TABLE_SCHEMA=b.TABLE_SCHEMA)  t  where  t.table_type='BASE TABLE')  c   where c.TABLE_SCHEMA not in ('information_schema','city_db','mysql','performance_schema','sys')

 postgresql：

脚本均需要在public下面运行

select 
    t1.table_catalog as table_schema,
    t1."table_name" as table_name,
    t2.obj_description as table_comment,
    t1."column_name" as column_name,
    t1.col_description as column_commente,
    t1.udt_name as column_type from (
    SELECT
    base.table_catalog,
    base."table_name",
        base."column_name",
        col_description ( t1.oid, t2.attnum ),
        base.udt_name
    FROM
        information_schema.COLUMNS base,
        pg_class t1,
        pg_attribute t2 
    WHERE
        base."table_schema" = 'public' and
     t1.relname = base."table_name" 
        AND t2.attname = base."column_name" 
        AND t1.oid = t2.attrelid 
        AND t2.attnum > 0 ) t1 left join 
    (SELECT
        "table_name",
        table_catalog,
        obj_description ( oid, 'pg_class' ) 
    FROM
        information_schema.tables t1,
        pg_class t2 
    WHERE
        table_schema = 'public' 
        AND t1."table_name" = t2.relname) t2  on  t1.table_name=t2.table_name  and t1.table_catalog=t2.table_catalog

    

hive:

需要放在hive元数据对应的mysql中运行

  SELECT
                    DBS.NAME db,
                    COLUMNS_V2.COLUMN_NAME col_name,
                    COLUMNS_V2.TYPE_NAME col_type,
                    COLUMNS_V2.COMMENT col_comment,
                    INTEGER_IDX integer_idx,
                    TBLS.TBL_ID tbl2,
                    TBLS.TBL_NAME tbl_name,
                    TBLS.CREATE_TIME create_time,
                    TABLE_PARAMS.PARAM_KEY tbl_comment,
                    TABLE_PARAMS.PARAM_VALUE tbl_comment_value
                FROM
                    hive.TBLS
                    LEFT JOIN hive.DBS ON TBLS.DB_ID = DBS.DB_ID
                    LEFT JOIN hive.SDS ON TBLS.SD_ID = SDS.SD_ID
                    LEFT JOIN hive.COLUMNS_V2 ON SDS.CD_ID = COLUMNS_V2.CD_ID
                    LEFT JOIN hive.TABLE_PARAMS ON TBLS.TBL_ID = TABLE_PARAMS.TBL_ID
                WHERE
                    DBS.NAME = 'rkjck'
                    AND TABLE_PARAMS.PARAM_KEY = 'comment'
                ORDER BY
                    INTEGER_IDX

oracle:

select * from (
	SELECT  
	atc.TABLE_NAME,	
	atc.COLUMN_NAME,	
	atc.DATA_TYPE,
	atc.data_length,
	acc.COMMENTS 
	FROM	ALL_TAB_COLUMNS atc	LEFT JOIN ALL_COL_COMMENTS acc ON (atc.TABLE_NAME = acc.TABLE_NAME AND atc.OWNER = acc.OWNER AND atc.COLUMN_NAME = acc.COLUMN_NAME)  WHERE	1 = 1		AND atc.OWNER = 'JW_USER' ) t1 left join 
(
SELECT TABLE_NAME,COMMENTS as table_comments
FROM ALL_TAB_COMMENTS where table_type='TABLE' and owner='JW_USER') t2  on t1.TABLE_NAME=t2.TABLE_NAME

不包含视图：

	
	select *  from (
	select * from (
	SELECT  
	atc.TABLE_NAME,	
	atc.COLUMN_NAME,	
	atc.DATA_TYPE,
	atc.data_length,
	acc.COMMENTS 
	FROM	ALL_TAB_COLUMNS atc	LEFT JOIN ALL_COL_COMMENTS acc ON (atc.TABLE_NAME = acc.TABLE_NAME AND atc.OWNER = acc.OWNER AND atc.COLUMN_NAME = acc.COLUMN_NAME)  WHERE	1 = 1		AND atc.OWNER = 'JW_USER' ) t1 left join 
(
SELECT TABLE_NAME,COMMENTS as table_comments,table_type
FROM ALL_TAB_COMMENTS where  owner='JW_USER') t2  on t1.TABLE_NAME=t2.TABLE_NAME ) m  where m.table_type not in 
(select table_type  from ALL_TAB_COMMENTS  where  table_type='VIEW')