desc.sql
code:SQL
--@desc <TBLNM-完全一致>
define TBLNM=&1
@column_format
col ID for 9999
col NULLABLE heading 'NULL?' for a8
col DATA_TYPE for a15
col PK for a4
set verify off
set timing off
set feed off
set pages 1000
select
t.OWNER
,t.TABLE_NAME
,m.COMMENTS
from
ALL_TABLES t
left outer join
ALL_TAB_COMMENTS m
on t.OWNER = m.OWNER
and t.TABLE_NAME = m.TABLE_NAME
where t.TABLE_NAME = upper('&TBLNM');
select
COLUMN_ID ID
,l.COLUMN_NAME
,cons.PK
,decode(l.NULLABLE,'N','NOT NULL',null) NULLABLE
,case when l.DATA_TYPE in ('CHAR','VARCHAR2','NVARCHAR2','NCHAR') then l.DATA_TYPE ||'('|| l.DATA_LENGTH ||')'
when l.DATA_TYPE in ('NUMBER') and l.DATA_PRECISION is not null then l.DATA_TYPE ||'('|| l.DATA_PRECISION ||','|| l.DATA_SCALE ||')'
else l.DATA_TYPE
end DATA_TYPE
,m.COMMENTS
from
ALL_TAB_COLUMNS l
left outer join
ALL_COL_COMMENTS m
on l.TABLE_NAME = m.TABLE_NAME
and l.COLUMN_NAME = m.COLUMN_NAME
left outer join
(
select
CO.TABLE_NAME
,'PK'||to_char(CC.POSITION) PK
,CC.COLUMN_NAME
from
ALL_CONSTRAINTS CO
inner join
ALL_CONS_COLUMNS CC
on CO.OWNER = CC.OWNER
and CO.CONSTRAINT_NAME = CC.CONSTRAINT_NAME
and CO.TABLE_NAME = CC.TABLE_NAME
where CONSTRAINT_TYPE = 'P'
) cons
on l.TABLE_NAME = cons.TABLE_NAME
and l.COLUMN_NAME = cons.COLUMN_NAME
where l.TABLE_NAME = upper('&TBLNM')
order by COLUMN_ID
;
@default_settings