[20220317]补充完善TPT 显示字段列的脚本.txt

--//tpt仅仅有comm.sql显示表注解的脚本，没有显示字段注解的脚本。在原始的comm.sql脚本增加查询字段的内容。
--//另外脚本支持模糊查询，查询最好规避它，不然输出太多。

$ cat colcomm.sql
-- Copyright 2018 Tanel Poder. All rights reserved. More info at http://tanelpoder.com
-- 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