Hows to get descriptions of tables out of Oracle

[scottyjohn]

Hi there,
Im looking for a way to get an export of the fields which are in the tables of our Database and I have very basic SQL knowledge and read only access.
Is there a simple SQL command to get this info or a utility?

John

 

[SantaMufasa]

John,

If you are in SQL*Plus, you have the SQL*Plus DESCRIBE command:

SQL> describe s_emp
 Name                    Null?    Type
 ----------------------- -------- ----------------
 ID                      NOT NULL NUMBER(7)
 LAST_NAME               NOT NULL VARCHAR2(25)
 FIRST_NAME                       VARCHAR2(25)
 USERID                           VARCHAR2(8)
 START_DATE                       DATE
 COMMENTS                         VARCHAR2(255)
 MANAGER_ID                       NUMBER(7)
 TITLE                            VARCHAR2(25)
 DEPT_ID                          NUMBER(7)
 SALARY                           NUMBER(11,2)
 COMMISSION_PCT                   NUMBER(4,2)

If, instead, you want a SQL query that displays such results, you can use my script that I named "desc.sql". (Since the script uses a SQL*Plus "ACCEPT...PROMPT" clause, you must save the code to a script such as "desc.sql", then run the script from SQL*Plus with a "@desc" command.)

"desc.sql" script code:

REM *******************************************************************
REM David L. Hunt (file author) distributes this and other 
REM files/scripts for educational purposes only, to illustrate the 
REM use or application of various computing techniques. The author
REM makes no warranty regarding this script's fitness for any 
REM industrial application or purpose nor is there any claim that
REM this or any similarly-distributed scripts are error free or
REM should be used for any purpose other than illustration.
REM *******************************************************************
set echo off
set verify off
set feedback off
set linesize 100
set pagesize 500
accept x prompt "Enter table name: "
col a format a18 heading "Column|Name" word_wrapped
col b format a13 heading "Data Type|and Length"
col "#" format 99 heading "Col| # "
select column_id "#",
        col.column_name a,
        col.data_type ||
           decode(substr(data_type,1,4),
             'DATE',null,
             'LONG',null,
             'CLOB',null,
             'BLOB',null,
             'VARC','('||ltrim(to_char(data_length,'9999'))||')',
             'CHAR','('||ltrim(to_char(data_length,'999'))||')',
             'FLOA','('||ltrim(to_char(data_precision,'99'))||
                    decode(data_scale,
                      0,null,
                      ','||ltrim(to_char(data_scale,'9999')))||')'
                   ,null,
             'NUMB','('||ltrim(to_char(data_precision,'99'))||
                    decode(data_scale,
                      0,null,
                      ','||ltrim(to_char(data_scale,'999')))||')'
                   ,null)
           b
 from user_tab_columns col
 where col.table_name  = upper('&x')
order by 1
/

Invocation of "desc.sql"

SQL> @desc
Enter table name: s_emp

Col Column             Data Type
 #  Name               and Length
--- ------------------ -------------
  1 ID                 NUMBER
  2 LAST_NAME          VARCHAR2(25)
  3 FIRST_NAME         VARCHAR2(25)
  4 USERID             VARCHAR2(8)
  5 START_DATE         DATE
  6 COMMENTS           VARCHAR2(255)
  7 MANAGER_ID         NUMBER
  8 TITLE              VARCHAR2(25)
  9 DEPT_ID            NUMBER
 10 SALARY             NUMBER
 11 COMMISSION_PCT     NUMBER

Let us know if either or these suggestions satisfies your need.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[cmmrfrds]

I use SQL to get the information. For example.

select table_name, column_name,
(case when data_type = 'VARCHAR2' then
data_type || '(' || data_length || ')'
when data_type = 'NUMBER' then
data_type || '(' || data_precision || ',' || data_scale || ')'
else data_type end) as dat_type,
(case when nullable = 'N' then
'NOT NULL'
else 'YES' end) as null_able
from sys.all_tab_columns
where owner = 'CLARITY' and table_name = 'CLARITY_TDL_TRAN'
Order by table_name, column_ID