[20221101]完善descz.sql指令碼.txt
[20221101]完善descz.sql指令碼.txt
--//前一段時間,使用我改寫的descz.sql指令碼(原始指令碼來自tpt descx.sql),發現許多不好的地方,第2個引數寫入太長.又是單引號,雙引
--//號,浪費時間.
--//例子:
@ descz lis.LIS_LOG_INFECTION "column_name in ('PAT_ID','PAT_BARCODE')"
--//如果寫成如下,簡單快捷:
@ desczz lis.LIS_LOG_INFECTION PAT_ID,PAT_BARCODE
--//修改如下,保留原來寫的descz.sql,建立新的desczz.sql指令碼.
$ cat desczz.sql
/* Formatted on 2022/10/10 21:04:36 (QP5 v5.227.12220.39754) */
-- 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 A20
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 : @ desczz TABLE_NAME column_name1,column_name2
PROMPT IF NOT INPUT <filters> ,USE "" or 1=1 or 1.
PROMPT
set termout off
column column_string new_value column_string format a200
select decode('&2','','1=1','1','1=1','1=1','1=1','column_name in ('||''''||replace(upper('&2'),',',''',''')||''')' ) column_string from dual ;
set termout on
BREAK ON desc_owner ON desc_table_name SKIP 1
SELECT owner desc_owner
,table_name desc_table_name
,sample_size
,last_analyzed
, 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 IN ('NVARCHAR2')
THEN
TO_CHAR (UTL_RAW.cast_to_nvarchar2 (low_value))
WHEN data_type = 'NUMBER'
THEN
TO_CHAR (UTL_RAW.cast_to_number (low_value))
WHEN data_type IN ('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'
)
)
)
WHEN data_type LIKE 'TIMESTAMP%'
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'
)
)
|| '.'
|| LTRIM
(
TO_CHAR
(
TO_NUMBER (SUBSTR (low_value, 15), 'XXXXXXXXX')
,'000000000'
)
)
)
END
trans_low
,CASE
WHEN data_type IN ('CHAR', 'VARCHAR2')
THEN
UTL_RAW.cast_to_varchar2 (high_value)
WHEN data_type IN ('NVARCHAR2')
THEN
TO_CHAR (UTL_RAW.cast_to_nvarchar2 (high_value))
WHEN data_type = 'NUMBER'
THEN
TO_CHAR (UTL_RAW.cast_to_number (high_value))
WHEN data_type IN ('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'
)
)
)
WHEN data_type LIKE 'TIMESTAMP%'
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'
)
)
|| '.'
|| LTRIM
(
TO_CHAR
(
TO_NUMBER (SUBSTR (high_value, 15), 'XXXXXXXXX')
,'000000000'
)
)
)
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 (&column_string)
ORDER BY owner, table_name, column_id
/
column column_string clear
--//測試:
SCOTT@test01p> @ desczz scott.emp EMPNO,sal,ename
eXtended describe of scott.emp
DISPLAY TABLE_NAME OF COLUMN_NAME INFORMATION.
INPUT OWNER.TABLE_NAME <filters>
SAMPLE : @ desczz TABLE_NAME column_name1,column_name2
IF NOT INPUT <filters> ,USE "" or 1=1 or 1.
Owner Table_Name SAMPLE_SIZE LAST_ANALYZED Col# Column Name Null? Type NUM_DISTINCT Density NUM_NULLS HISTOGRAM NUM_BUCKETS Low_value
---------- -------------------- ----------- ------------------- ---- -------------------- ---------- -------------------- ------------ -------------- ---------- --------------- ----------- ---------------
SCOTT EMP 14 2022-10-30 20:57:05 1 EMPNO NOT NULL NUMBER(4,0) 14 .07142857143 0 1 7369
14 2022-10-30 20:57:05 2 ENAME VARCHAR2(10) 14 .07142857143 0 1 ADAMS
14 2022-10-30 20:57:05 6 SAL NUMBER(7,2) 12 .08333333333 0 1 800
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/267265/viewspace-2921792/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- [20221010]完善descz.sql指令碼.txtSQL指令碼
- [20221101]完善gts.sql指令碼.txtSQL指令碼
- [20210506]完善tix指令碼.txt指令碼
- [20201202]完善sosi指令碼.txt指令碼
- [20210623]完善清除aud指令碼.txt指令碼
- [20231117]完善ashtt.sql指令碼.txtSQL指令碼
- [20211230]完善sql_id指令碼.txtSQL指令碼
- [20211122]完善descx.sql指令碼.txtSQL指令碼
- [20221012]完善spsw.sql指令碼.txtSQL指令碼
- [20230203]完善awr.sql指令碼.txtSQL指令碼
- [20230123]完善curheapz.sql指令碼.txtSQL指令碼
- [20210407]完善ti.sql指令碼.txtSQL指令碼
- [20210125]完善hide.sql指令碼.txtIDESQL指令碼
- [20191111]完善bind_cap.sql指令碼.txtSQL指令碼
- [20220217]完善tpt gts.sql指令碼.txtSQL指令碼
- [20220510]完善tpt expandz.sql指令碼.txtSQL指令碼
- [20211202]完善d_buffer.sql指令碼.txtSQL指令碼
- [20211129]完善tpt tablist.sql指令碼.txtSQL指令碼
- [20211130]完善tpt t.sql指令碼.txtSQL指令碼
- [20211129]完善tpt killi.sql指令碼.txtSQL指令碼
- [20211126]完善tpt pr.sql指令碼.txtSQL指令碼
- [20230414]完善seg2.sql指令碼.txtSQL指令碼
- [20230210]建立完善swcnm.sql指令碼.txtSQL指令碼
- [20221208]完善bind_cap.sql指令碼.txtSQL指令碼
- [20220311]完善ash_wait_chains指令碼.txtAI指令碼
- [20220309]完善shp4.sql指令碼.txtSQL指令碼
- [20231025]完善tpt的trans.sql指令碼.txtSQL指令碼
- [20241114]建立完善ext_kglob.sh指令碼.txt指令碼
- [20220111]完善tpt ashash_index_helper指令碼.txtIndex指令碼
- [20220323]完善tpt get_trace.sql指令碼.txtSQL指令碼
- [20220823]完善tpt的ashtop.sql指令碼.txtSQL指令碼
- [20221208]完善bind_cap_awr.sql指令碼.txtSQL指令碼
- [20190416]完善shared latch測試指令碼2.txt指令碼
- [20220129]完善tpt ash ash_index_helperx指令碼.txtIndex指令碼
- [20211111]補充完善ash_wait_chains指令碼.txtAI指令碼
- [20230302]建立完善tpt o2.sql指令碼.txtSQL指令碼
- [20230203]建立完善sp1x.sql指令碼.txtSQL指令碼
- [20220519]完善tpt dash_wait_chains2.sql指令碼.txtAISQL指令碼