[20211122]完善descx.sql脚本.txt

[20211122]完善descx.sql脚本.txt --// 按照tpt descx.sql脚本修改一下,增加一些功能,我主要不想使用display_raw函数，而且还有许多系统使用11g，无法在sql语句中定义函数。 --// 另外就是我们生产系统表字段太多了，有必要加入一些过滤，显示特定字段。 $ cat descz.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. COL desc_column_id              HEAD "Col#" FOR A4 COL desc_column_name            HEAD "Column Name" FOR A30 COL desc_data_type              HEAD "Type" FOR A20 WORD_WRAP COL desc_nullable               HEAD "Null?" FOR A10 COL desc_density        HEAD "Density" FOR 9.99999999999 COL desc_owner      HEAD Owner         FOR A10 COL desc_table_name HEAD Table_Name    FOR A20 COL trans_low           HEAD "Low_value" FOR A40 COL trans_high          HEAD "High_value" FOR A40 prompt eXtended describe of &1 PROMPT PROMPT DISPLAY TABLE_NAME OF COLUMN_NAME INFORMATION. PROMPT INPUT   OWNER.TABLE_NAME  <filters> PROMPT SAMPLE  : @ TAB_LH TABLE_NAME "column_id between 3 and 5" PROMPT IF NOT INPUT <filters> ,USE "1=1" . PROMPT break on desc_owner on desc_table_name skip 1 SELECT   owner       desc_owner,   table_name  desc_table_name,         CASE WHEN hidden_column = 'YES' THEN 'H' ELSE ' ' END||         LPAD(column_id,3)       desc_column_id,         column_name     desc_column_name,         CASE WHEN nullable = 'N' THEN 'NOT NULL' ELSE NULL END AS desc_nullable,         data_type||CASE --                                      WHEN data_type = 'NUMBER' THEN '('||data_precision||CASE WHEN data_scale = 0 THEN NULL ELSE ','||data_scale END||')'                                         WHEN data_type = 'NUMBER' THEN '('||data_precision||','||data_scale||')'                                         ELSE '('||data_length||')'                                 END AS desc_data_type, --      data_default,         num_distinct,         density             desc_density,         num_nulls,   CASE WHEN histogram = 'NONE'  THEN null ELSE histogram END histogram,         num_buckets,     CASE        WHEN data_type IN ('CHAR', 'VARCHAR2')        THEN           UTL_RAW.cast_to_varchar2 (low_value)        WHEN data_type = 'NUMBER'        THEN           TO_CHAR (UTL_RAW.cast_to_number (low_value))        WHEN data_type = 'DATE'        THEN           RTRIM (                 LTRIM (                    TO_CHAR (                           100                         * (  TO_NUMBER (SUBSTR (low_value, 1, 2), 'XX')                            - 100)                       + (TO_NUMBER (SUBSTR (low_value, 3, 2), 'XX') - 100),                       '0000'))              || '-'              || LTRIM (                    TO_CHAR (TO_NUMBER (SUBSTR (low_value, 5, 2), 'XX'),                             '00'))              || '-'              || LTRIM (                    TO_CHAR (TO_NUMBER (SUBSTR (low_value, 7, 2), 'XX'),                             '00'))              || ' '              || LTRIM (                    TO_CHAR (                       TO_NUMBER (SUBSTR (low_value, 9, 2), 'XX') - 1,                       '00'))              || ':'              || LTRIM (                    TO_CHAR (                       TO_NUMBER (SUBSTR (low_value, 11, 2), 'XX') - 1,                       '00'))              || ':'              || LTRIM (                    TO_CHAR (                       TO_NUMBER (SUBSTR (low_value, 13, 2), 'XX') - 1,                       '00')))     END            trans_low,     CASE        WHEN data_type IN ('CHAR', 'VARCHAR2')        THEN           UTL_RAW.cast_to_varchar2 (high_value)        WHEN data_type = 'NUMBER'        THEN           TO_CHAR (UTL_RAW.cast_to_number (high_value))        WHEN data_type = 'DATE'        THEN           RTRIM (                 LTRIM (                    TO_CHAR (                           100                         * (  TO_NUMBER (SUBSTR (high_value, 1, 2), 'XX')                            - 100)                       + (TO_NUMBER (SUBSTR (high_value, 3, 2), 'XX') - 100),                       '0000'))              || '-'              || LTRIM (                    TO_CHAR (TO_NUMBER (SUBSTR (high_value, 5, 2), 'XX'),                             '00'))              || '-'              || LTRIM (                    TO_CHAR (TO_NUMBER (SUBSTR (high_value, 7, 2), 'XX'),                             '00'))              || ' '              || LTRIM (                    TO_CHAR (                       TO_NUMBER (SUBSTR (high_value, 9, 2), 'XX') - 1,                       '00'))              || ':'              || LTRIM (                    TO_CHAR (                       TO_NUMBER (SUBSTR (high_value, 11, 2), 'XX') - 1,                       '00'))              || ':'              || LTRIM (                    TO_CHAR (                       TO_NUMBER (SUBSTR (high_value, 13, 2), 'XX') - 1,                       '00')))     END        trans_high         --,'--' desc_succeeded FROM         dba_tab_cols WHERE         upper(table_name) LIKE                                 upper(CASE                                         WHEN INSTR('&1','.') > 0 THEN                                             SUBSTR('&1',INSTR('&1','.')+1)                                         ELSE                                             '&1'                                         END                                      ) AND     owner LIKE                 CASE WHEN INSTR('&1','.') > 0 THEN                         UPPER(SUBSTR('&1',1,INSTR('&1','.')-1))                 ELSE                         user                 END --AND column_name = decode('&&2','',column_name,upper('&&2')) AND ( &&2 ) ORDER BY   owner,   table_name,         column_id / 1.环境： SCOTT@book> @ ver BANNER -------------------------------------------------------------------------------- Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production SCOTT@book> create index i_emp_hiredate on emp(trunc(hiredate)); Index created. 2.测试： SCOTT@book> @ descz scott.emp 1=1 eXtended describe of scott.emp DISPLAY TABLE_NAME OF COLUMN_NAME INFORMATION. INPUT   OWNER.TABLE_NAME  <filters> SAMPLE  : @ TAB_LH TABLE_NAME "column_id between 3 and 5" IF NOT INPUT <filters> ,USE "1=1" . Owner      Table_Name           Col# Column Name                    Null?      Type                 NUM_DISTINCT        Density  NUM_NULLS HISTOGRAM       NUM_BUCKETS Low_value                                High_value ---------- -------------------- ---- ------------------------------ ---------- -------------------- ------------ -------------- ---------- --------------- ----------- ---------------------------------------- ---------------------------------------- SCOTT      EMP                     1 EMPNO                          NOT NULL   NUMBER(4,0)                    14   .07142857143          0                           1 7369                                     7934                                    2 ENAME                                     VARCHAR2(10)                   14   .07142857143          0                           1 ADAMS                                    WARD                                    3 JOB                                       VARCHAR2(9)                     5   .20000000000          0                           1 ANALYST                                  SALESMAN                                    4 MGR                                       NUMBER(4,0)                     6   .16666666667          1                           1 7566                                     7902                                    5 HIREDATE                                  DATE(7)                        13   .07692307692          0                           1 1980-12-17 00:00:00                      1987-05-23 00:00:00                                    6 SAL                                       NUMBER(7,2)                    12   .08333333333          0                           1 800                                      5000                                    7 COMM                                      NUMBER(7,2)                     4   .25000000000         10                           1 0                                        1400                                    8 DEPTNO                                    NUMBER(2,0)                     3   .33333333333          0                           1 10                                       30                                 H    SYS_NC00009$                              DATE(7)                                                                                 -- ::                                    -- :: 9 rows selected. --//分析表。 SCOTT@book> @ descz emp "column_name='SAL' or hidden_column='YES'" eXtended describe of emp DISPLAY TABLE_NAME OF COLUMN_NAME INFORMATION. INPUT   OWNER.TABLE_NAME  <filters> SAMPLE  : @ TAB_LH TABLE_NAME "column_id between 3 and 5" IF NOT INPUT <filters> ,USE "1=1" . Owner      Table_Name           Col# Column Name                    Null?      Type                 NUM_DISTINCT        Density  NUM_NULLS HISTOGRAM       NUM_BUCKETS Low_value                                High_value ---------- -------------------- ---- ------------------------------ ---------- -------------------- ------------ -------------- ---------- --------------- ----------- ---------------------------------------- ---------------------------------------- SCOTT      EMP                     6 SAL                                       NUMBER(7,2)                    12   .08333333333          0                           1 800                                      5000                                 H    SYS_NC00009$                              DATE(7)                        13   .07692307692          0                           1 1980-12-17 00:00:00                      1987-05-23 00:00:00 SCOTT@book> @ descz emp "column_id between 3 and 5" eXtended describe of emp DISPLAY TABLE_NAME OF COLUMN_NAME INFORMATION. INPUT   OWNER.TABLE_NAME  <filters> SAMPLE  : @ TAB_LH TABLE_NAME "column_id between 3 and 5" IF NOT INPUT <filters> ,USE "1=1" . Owner      Table_Name           Col# Column Name                    Null?      Type                 NUM_DISTINCT        Density  NUM_NULLS HISTOGRAM       NUM_BUCKETS Low_value                                High_value ---------- -------------------- ---- ------------------------------ ---------- -------------------- ------------ -------------- ---------- --------------- ----------- ---------------------------------------- ---------------------------------------- SCOTT      EMP                     3 JOB                                       VARCHAR2(9)                     5   .20000000000          0                           1 ANALYST                                  SALESMAN                                    4 MGR                                       NUMBER(4,0)                     6   .16666666667          1                           1 7566                                     7902                                    5 HIREDATE                                  DATE(7)                        13   .07692307692          0                           1 1980-12-17 00:00:00                      1987-05-23 00:00:00