Vertica获取元数据@2018-11-23

获取表名，表名注释，字段名，字段名注释，字段类型信息

SELECT T.TABLE_SCHEMA,
       T.TABLE_NAME,
       T.TABLE_COMMENT,
       T.COLUMN_NAME,
       T.COLUMN_DATA_TYPE,
       T4.COMMENT COLUMN_COMMENT
  FROM (SELECT UPPER(T1.TABLE_SCHEMA) TABLE_SCHEMA,
               T1.TABLE_NAME,
               T3.COMMENT TABLE_COMMENT,
               T2.COLUMN_NAME,
               T2.DATA_TYPE COLUMN_DATA_TYPE,
               UPPER(T1.TABLE_NAME || '_B0.' || T2.COLUMN_NAME) COLUMN_OBJECT,
               T1.TABLE_ID,
               TO_NUMBER(SUBSTRING(T2.COLUMN_ID,
                                   INSTR(T2.COLUMN_ID, '-') + 1,
                                   2)) COLUMN_SORT
          FROM V_CATALOG.TABLES T1
          LEFT JOIN V_CATALOG.COLUMNS T2
            ON T1.TABLE_ID = T2. TABLE_ID
          LEFT JOIN V_CATALOG.COMMENTS T3
            ON T1.TABLE_ID = T3.OBJECT_ID) T
  LEFT JOIN V_CATALOG.COMMENTS T4
    ON T.COLUMN_OBJECT = UPPER(OBJECT_NAME)
 WHERE TABLE_SCHEMA = 'DW'
 ORDER BY T.TABLE_NAME DESC, COLUMN_SORT