Re: How to get the field attributes from a table

From: John Strange <jstrange_at_imtn.dsccc.com>
Date: 5 Nov 1998 12:38:49 GMT
Message-ID: <71s68p$3a8$1@relay1.dsccc.com>

Here run this

/*****************************************************************
*
*       desc_tbl.sql - Describe user's tables with comments.
*
*       usage - sqlplus scott/tiger @desc_tbl.sql
*
*       output - desc_tbl.lis  (180 col)

*

*****************************************************************/
 

column data_type format a15
column column_name format a25
column comments format a70
column table_name format a23  

set linesize 180
set pagesize 6000
set pause off
set term off  

break on table_name skip 1 nodup  

spool desc_tbl.lis  

select tc.table_name

  , tc.column_name
  , tc.nullable
  , decode (rtrim (tc.data_type),

'CHAR', DATA_TYPE || '(' || DATA_LENGTH || ')',
'NUMBER', DATA_TYPE || '(' || DATA_PRECISION || '.' || DATA_SCALE || ')',
'DATE', DATA_TYPE,
'VARCHAR2', DATA_TYPE || '(' || DATA_LENGTH || ')',
'LONG', DATA_TYPE, data_type

    ) DATA_TYPE
  , cc.comments
 FROM user_tab_columns tc, user_col_comments cc WHERE cc.table_name = tc.table_name
  and cc.column_name = tc.column_name
order by tc.table_name, column_id
/
spool off
exit ;

/****************** end        desc_tbl.sql ********************************/

Small Keung (kkho_at_cse.cuhk.edu.hk) wrote: : As I created a table from Oracle database, how can I get the field attributes (e.g. Field name, length, types) by SQL commands?

: -- 
: By 小強小強小小強
: 上上下下左左右右...

--
While Alcatel may claim ownership of all my ideas (on or off the job), Alcatel does not claim any responsibility for them. Warranty expired when u opened this article and I will not be responsible for its contents or use. Received on Thu Nov 05 1998 - 06:38:49 CST