[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
-- 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