[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 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 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
PROMPT SAMPLE : @ desczz TABLE_NAME column_name1,column_name2
PROMPT IF NOT INPUT
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
SAMPLE : @ desczz TABLE_NAME column_name1,column_name2
IF NOT INPUT
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
[20221101]完善descz.sql脚本.txt
-
lfree
2022-11-03 20:09:49 -
Oracle
- 原创