[20221101]完善descz.sql脚本.txt

来源：这里教程网时间：2026-03-03 18:04:52 作者：

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

编辑推荐：

返回列表

[20221101]完善descz.sql脚本.txt

编辑推荐：

相关推荐

excel表格怎样在数字前输入0

excel表格里怎么替换数据

excel表格里面如何插入圆形

excel表格里怎么添加表格数据透视表

excel表格中怎么添加线边框

excel表格里怎样设置条件自动排序

excel表格例怎样导入数据库

Excel表格如何设置数据排序

excel表格里怎么选择性粘贴快捷键

excel表格怎么插入分隔符号

excel表格怎么设置多行多列数据内容排序

Excel表格如何设置行和列相互转换

excel表格连接公式怎么使用

excel表格怎样链接到cad中

雷神推出 MIX PRO II 迷你主机：基于 Ultra 200H，玻璃上盖 + ARGB 灯效

制造商 Musnap 推出彩色墨水屏电纸书 Ocean C：支持手写笔、第三方安卓应用

最新软件资讯

热文推荐

天极热推