use metastore;

SELECT DBS.NAME as OWNER,
       TBLS.TBL_ID,
       TBLS.TBL_NAME as OBJECT_NAME,
       TBL_COMMENTS.TBL_COMMENT as OBJECT_DESCRIPTION,
       INTEGER_IDX,
       COLUMNS_V2.COLUMN_NAME,
       COLUMNS_V2.COMMENT as COLUMN_DESCRIPTION,
       COLUMNS_V2.TYPE_NAME as DATA_TYPE
FROM DBS

JOIN TBLS
ON DBS.DB_ID = TBLS.DB_ID

JOIN SDS
ON TBLS.SD_ID = SDS.SD_ID

JOIN COLUMNS_V2
ON COLUMNS_V2.CD_ID = SDS.CD_ID

LEFT JOIN (
	SELECT DISTINCT
           TBL_ID,
           TBL_COMMENT
      FROM (SELECT TBLS.TBL_ID TBL_ID,
                   TABLE_PARAMS.PARAM_KEY,
                   TABLE_PARAMS.PARAM_VALUE,
CASE WHEN TABLE_PARAMS.PARAM_KEY = 'comment'
THEN TABLE_PARAMS.PARAM_VALUE
ELSE ''
END TBL_COMMENT
FROM TBLS
JOIN TABLE_PARAMS
ON TBLS.TBL_ID = TABLE_PARAMS.TBL_ID
WHERE TABLE_PARAMS.PARAM_KEY ='comment') TBL_COMMENTS_INTERNAL) TBL_COMMENTS
ON TBLS.TBL_ID = TBL_COMMENTS.TBL_ID
WHERE 1=1
AND DBS.NAME IN ('lake_offline', 'lake_online')
AND TBLS.TBL_TYPE = 'MANAGED_TABLE'
order by DBS.NAME,
TBLS.TBL_NAME,
INTEGER_IDX
;


