等效DESC查詢

llnnmc發表於2017-06-29

以下SQL查詢等效於DESC命令

col name for a20
col type for a20
col nullable for a10
col defaults for a10
col comments for a50
select a.column_name name,
       data_type ||
       decode(data_type,
              'date',
              '',
              'clob',
              '',
              'blob',
              '',
              'bfile',
              '',
              'float',
              '',
              'long raw',
              '',
              'long',
              '',
              'raw',
              '(' || to_char(data_length) || ')',
              (decode(sign(instr(data_type, 'char')),
                      1,
                      '(' || to_char(data_length) || ')',
                      (decode(substr(data_type, 1, 9),
                              'timestamp',
                              '',
                              (decode(nvl(data_precision, -1),
                                      -1,
                                      '',
                                      (decode(nvl(data_scale, 0),
                                              0,
                                              '(' || to_char(data_precision) || ')',
                                              '(' || to_char(data_precision) || ',' ||
                                              to_char(data_scale) || ')'))))))))) type,
       a.nullable,
       a.data_default defaults,
       b.comments
  from dba_tab_columns a, dba_col_comments b
 where b.table_name = a.table_name
   and a.owner = b.owner
   and a.column_name = b.column_name
   and a.table_name = upper('&tablename')
   and a.owner = upper('&username')
 order by column_id;


Enter value for tablename: emp

Enter value for username: scott


NAME                 TYPE                 NULLABLE   DEFAULTS   COMMENTS
-------------------- -------------------- ---------- ---------- ------------------------------
EMPNO                NUMBER(4)            N
ENAME                VARCHAR2             Y
JOB                  VARCHAR2             Y
MGR                  NUMBER(4)            Y
HIREDATE             DATE                 Y
SAL                  NUMBER(7,2)          Y
COMM                 NUMBER(7,2)          Y
DEPTNO               NUMBER(2)            Y


來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/28974745/viewspace-2141376/,如需轉載,請註明出處,否則將追究法律責任。

相關文章