[20220317]補充完善TPT 顯示欄位列的指令碼.txt

[20220317]補充完善TPT 顯示欄位列的指令碼.txt

--//tpt僅僅有comm.sql顯示錶註解的指令碼，沒有顯示欄位註解的指令碼。在原始的comm.sql指令碼增加查詢欄位的內容。
--//另外指令碼支援模糊查詢，查詢最好規避它，不然輸出太多。

$ cat colcomm.sql
-- Copyright 2018 Tanel Poder. All rights reserved. More info at
-- Licensed under the Apache License, Version 2.0. See LICENSE.txt for terms & conditions.

prompt Not listing tables without comments...
prompt Input argument owner.table_name column  or owner.table_name ''

COLUMN comm_comments HEADING COMMENTS FORMAT a120 WORD_WRAP
COLUMN comm_owner    HEADING OWNER FORMAT A20 WRAP
COLUMN comm_table_name HEADING TABLE_NAME FORMAT A30
COLUMN comm_column_name HEADING COLUMN_NAME FORMAT A30

SELECT
    owner                   comm_owner
  ,     table_name              comm_table_name
  , comments comm_comments
FROM
        all_tab_comments
WHERE
  comments is not null
AND
  upper(table_name) LIKE
        upper(CASE
          WHEN INSTR('&1','.') > 0 THEN
              SUBSTR('&1',INSTR('&1','.')+1)
          ELSE
              '&1'
          END
             ) ESCAPE '\'
AND owner LIKE
    CASE WHEN INSTR('&1','.') > 0 THEN
      UPPER(SUBSTR('&1',1,INSTR('&1','.')-1))
    ELSE
      user
    END ESCAPE '\'
/


SELECT
    owner                   comm_owner
  ,     table_name              comm_table_name
  , column_name             comm_column_name
  , comments comm_comments
FROM
        all_col_comments
WHERE
  comments is not null
AND
  upper(table_name) LIKE
        upper(CASE
          WHEN INSTR('&1','.') > 0 THEN
              SUBSTR('&1',INSTR('&1','.')+1)
          ELSE
              '&1'
          END
             ) ESCAPE '\'
AND owner LIKE
    CASE WHEN INSTR('&1','.') > 0 THEN
      UPPER(SUBSTR('&1',1,INSTR('&1','.')-1))
    ELSE
      user
    END ESCAPE '\'
and column_name like  '%'||nvl(upper('&2'),column_name)||'%'
/

--//輸出例子如下：
SYS@book> @ colcomm sys.DBA_TAB_MODIFICATIONS name
Not listing tables without comments...
OWNER                TABLE_NAME                     COMMENTS
-------------------- ------------------------------ ----------------------------------------------------------
SYS                  DBA_TAB_MODIFICATIONS          Information regarding modifications to tables

OWNER                TABLE_NAME                     COLUMN_NAME                    COMMENTS
-------------------- ------------------------------ ------------------------------ ---------------------------
SYS                  DBA_TAB_MODIFICATIONS          TABLE_NAME                     Modified table
SYS                  DBA_TAB_MODIFICATIONS          PARTITION_NAME                 Modified partition
SYS                  DBA_TAB_MODIFICATIONS          SUBPARTITION_NAME              Modified subpartition