So what do my tables look like anyway (Part 1)

---

"Where're you gonna find Spock's brain?" - Doctor Leonard McCoy, Star Trek, The Original Series

---

I recently had a developer come to me and ask me for the table structures of every table in this particular schema created by a previous employee. Of course, there's no DDL script anywhere to be found.

So, you could actually extract a layout of the tables from the data dictionary in Oracle.

The following script (which I don't claim to be exactly perfect would be a way to extract your table layout.

First, let's create a couple of dummy tables:

create table Data_Type_Table
( col01 varchar2(100),
  col02 nvarchar2(200),
  col03 char(50) not null,
  col04 nchar(80),
  col05 number(12),
  col06 number(12,5),
  col07 number,
  col08 long,
  col09 date not null,
  col10 timestamp,
  col11 timestamp(6),
  col12 timestamp with time zone,
  col13 timestamp with local time zone,
  col14 interval year(5) to month,
  col15 interval day(4) to second(3),
  col16 raw(12),
  col17 rowid,
  col18 urowid,
  col19 mlslabel,
  col20 clob,
  col21 nclob,
  col22 blob,
  col23 bfile,
  col24 XMLType);

create table data_table
( mycol1 number,
  mycol2 varchar2(400) not null);

The the following script will get you your information (You can tweak the size of the width of the columns to fit your own tables too)

set lines 132
set pages 10000
column table_name format a30
column column_name format a30
column data_type format a40
column nullable format a8

break on table_name skip 2;

select 
  table_name, 
  column_name,
  decode(data_type, 
   'VARCHAR2',data_type||'('||data_length||')',
   'CHAR'    ,data_type||'('||data_length||')',
   'NVARCHAR2'    ,data_type||'('||data_length||')',
   'NCHAR'    ,data_type||'('||data_length||')',
   'NUMBER' ,decode(data_precision, 
                    null, data_type, 
                    data_type||'('||data_precision||','||data_scale||')'),
   data_type) as DATA_TYPE,
  decode(nullable, 'Y', ' ', 'NOT NULL') as NULLABLE
from dba_tab_columns
where owner = 'MYSCHEMA'
order by table_name, column_id

And the output looks a little something like this:

TABLE_NAME      COLUMN_NAME DATA_TYPE                           NULLABLE
--------------- ----------- ----------------------------------- --------
DATA_TABLE      MYCOL1      NUMBER
                MYCOL2      VARCHAR2(400)                       NOT NULL


DATA_TYPE_TABLE COL01       VARCHAR2(100)
                COL02       NVARCHAR2(400)
                COL03       CHAR(50)                            NOT NULL
                COL04       NCHAR(160)
                COL05       NUMBER(12,0)
                COL06       NUMBER(12,5)
                COL07       NUMBER
                COL08       LONG
                COL09       DATE                                NOT NULL
                COL10       TIMESTAMP(6)
                COL11       TIMESTAMP(6)
                COL12       TIMESTAMP(6) WITH TIME ZONE
                COL13       TIMESTAMP(6) WITH LOCAL TIME ZONE
                COL14       INTERVAL YEAR(5) TO MONTH
                COL15       INTERVAL DAY(4) TO SECOND(3)
                COL16       RAW
                COL17       ROWID
                COL18       UROWID
                COL19       MLSLABEL
                COL20       CLOB
                COL21       NCLOB
                COL22       BLOB
                COL23       BFILE
                COL24       XMLTYPE