oracle 使用sql查詢表註釋和列註釋及資料型別等

softwareDragon發表於2020-10-28

SELECT 
t1.Table_Name || chr(13) || t3.comments       AS "表名稱及說明",
     ROWNUM ,
       t1.Column_Name                                AS "欄位名稱",
       t1.DATA_TYPE             AS "資料型別",
             t1.DATA_LENGTH    AS "長度",
       t1.NullAble                                   AS "是否為空",
       t2.Comments                                   AS "欄位說明",
       t1.Data_Default "預設值"
       --t4.created                                  AS "建表時間"
               --t3.comments                                 AS "表說明",
  FROM cols t1
  LEFT JOIN user_col_comments t2 
         ON t1.Table_name = t2.Table_name
        AND t1.Column_Name = t2.Column_Name
  LEFT JOIN user_tab_comments t3 
         ON t1.Table_name = t3.Table_name
  LEFT JOIN user_objects t4 
         ON t1.table_name = t4.OBJECT_NAME
  WHERE NOT EXISTS (SELECT t4.Object_Name
          FROM User_objects t4
         WHERE t4.Object_Type = 'TABLE'
           AND t4.Temporary = 'Y'
           AND t4.Object_Name = t1.Table_Name)
                     and t1.TABLE_NAME='AAAA'
  ORDER BY t1.Table_Name, t1.Column_ID

備註:紅色AAAA代表表名

轉載自哪個地方忘了

相關文章