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

[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