-- listcol.sql		Produces a listing of a table and associated columns (fields).

--**Use this information and these scripts at your own risk. As a condition of using these scripts and information from this site, you agree to hold harmless both the University of Arkansas Cooperative Extension Service and Bruce Knox for any problems that they may cause or other situations that may arise from their use, and that neither the Extension Service nor I will be held liable for those consequences. The scripts and information are provided "as is" without warranty, implied or otherwise. Limitation of liability will be the amount paid to the University of Arkansas specifically for this information. (It was free:) 

set termout off
/*  Produces a listing of a table and associated columns (fields).                 
    You will be prompted to enter a table prefix.
	
    by Steven Rea    (modified to include table desc in heading by bknox 8/98) */    

SET SHOWMODE OFF
SET TIMING OFF
set termout on
set echo off
set feedback off
set verify off
set recsep off
set timing off
set pagesize 44
set linesize 106
set newpage  0
set space    1
set arraysize 5

CLEAR COLUMNS
CLEAR BREAK
CLEAR COMPUTE

column COL_COMMENT format a38  word_wrap
column COLUMN_NAME format a30 
column DATA_SCALE  format 9    heading  'DEC'                      
column DATA_TYPE   format a8   heading  'TYPE'
column LENGTH      format 9999  heading  'LENGTH'
column NULLABLE    format a5   heading  'NULL?'                 

column TABLE_NAME  NEW_VALUE tablevar format a10 HEADING 'Table Name '
column Desc        NEW_VALUE descvar  format a65;
column TODAY       noprint   new_value   date_var

break on TABLE_NAME SKIP 3
!clear
ACCEPT TABLENAME      CHAR PROMPT 'List Table: ' 

select UPPER('&&TABLENAME') TABLE_NAME, to_char(SYSDATE,'mm/dd/yy') TODAY from DUAL;

SELECT SUBSTR(table_name,1,12) "Table", SUBSTR(comments,1,65) "Desc"
  FROM all_tab_comments
 WHERE table_name=UPPER('&&TABLENAME');

set termout off
SET HEADING ON

ttitle left 'Date: ' date_var -
  center tablevar ': ' descvar -
  right  'Page ' format 99  SQL.PNO -
  skip 2 
TTITLE ON

!rm -f listcol.lst
spool listcol

SELECT C.TABLE_NAME TABLE_NAME,
                C.COLUMN_NAME,
                C.DATA_TYPE,
                NVL(C.DATA_PRECISION,C.DATA_LENGTH) LENGTH,
                C.DATA_SCALE,
                C.NULLABLE, 
                U.COMMENTS COL_COMMENT
       from   ALL_TAB_COMMENTS   A,       
              ALL_COL_COMMENTS   U, 
              ALL_TAB_COLUMNS    C      
       where    C.COLUMN_NAME = U.COLUMN_NAME
         and    A.TABLE_NAME  = U.TABLE_NAME
         and    A.TABLE_NAME  = C.TABLE_NAME
         and    C.TABLE_NAME  like upper('&&TABLENAME') || '%'
       order by C.TABLE_NAME,
                C.COLUMN_ID;
spool off
set feedback on
set verify on
set pagesize 24
set linesize 80
set newpage 1
set arraysize 20
set timing on
undefine TABLENAME
set echo on

TTITLE OFF
BTITLE OFF

CLEAR COLUMNS
CLEAR BREAKS
CLEAR COMPUTES

-- This script is available "as is".  See Disclaimer http://www.uaex.edu/bknox/
set termout on