Get Table Details

Sometimes when you're documenting you need to get a list of fields, fieldnames, types and their length. This little piece of SQL marries an Oracle system table and two PeopleSoft tables to furnish this information.

select ATC.COLUMN_ID,
       ATC.COLUMN_NAME,
       LAB.LONGNAME,
       CASE
         WHEN FLD.FIELDTYPE = 0 THEN 'Character'
         WHEN FLD.FIELDTYPE = 1 THEN 'Long Character'
         WHEN FLD.FIELDTYPE = 2 THEN 'Number'
         WHEN FLD.FIELDTYPE = 3 THEN 'Signed Number'
         WHEN FLD.FIELDTYPE = 4 THEN 'Date'
         WHEN FLD.FIELDTYPE = 5 THEN 'Time'
         WHEN FLD.FIELDTYPE = 6 THEN 'Date Time'
         WHEN FLD.FIELDTYPE = 8 THEN 'Image or Attachment'
         WHEN FLD.FIELDTYPE = 9 THEN 'ImageReference'
         ELSE                        'Unknown'
       END as TYPE,
       FLD.LENGTH
  from ALL_TAB_COLUMNS ATC,
       PSDBFLDLABL     LAB,
       PSDBFIELD       FLD
 where ATC.TABLE_NAME    = 'PS_JOB'
   and ATC.COLUMN_NAME   = LAB.FIELDNAME
   and LAB.DEFAULT_LABEL = 1
   and LAB.FIELDNAME     = FLD.FIELDNAME
 order by ATC.COLUMN_ID Asc

Getting Details Of A Table's Columns

The following Oracle SQL gets the columns in a table.

-- -------------------
-- Get Table's Columns
-- -------------------
select COLUMN_NAME,
       DATA_TYPE
  from ALL_TAB_COLUMNS
 where TABLE_NAME = 'PS_TY_TL_SDF_EVT'

Listing All PeopleSoft System tables

All PeopleSoft system tables can be dumped with: -

select RECNAME,
       RECDESCR
  from PSRECDEFN
 where RECNAME like 'PS%'
   and RECDESCR <> ' '

Finding a table name

select *
from dba_tables
where table_name like '%<table_name>';

Listing a tables Indexes

select index_name
from dba_indexes
where table_name=<table_name>