Login | Register   
Twitter
RSS Feed
Download our iPhone app
TODAY'S HEADLINES  |   ARTICLE ARCHIVE  |   FORUMS  |   TIP BANK
Browse DevX
Sign up for e-mail newsletters from DevX


Tip of the Day
Language: Enterprise
Expertise: Intermediate
Jun 7, 2001

A Script That Lists All Tables and Their Column Names With Data Types


This script creates a report of all user owned tables and their columns with data types and nullability information.
 
SET HEADING OFF
SET LINESIZE 400
SET PAGESIZE 1000
BREAK ON TABLE_NAME SKIP 3
SPOOL C:/TAB_COL_REP.TXT;
SELECT TABLE_NAME, COLUMN_NAME, DECODE(NULLABLE,'N',
'NOT NULL','Y','') NULLABLE,
     DECODE

		DATA_TYPE, 'NUMBER',
		TRIM(DATA_TYPE) || '(' || DATA_PRECISION 
|| DECODE ( DATA_SCALE, '0' ,
'' , ',' || DATA_SCALE )  || ')',
		TRIM(DATA_TYPE) || '(' || DATA_LENGTH || ')' ) 
Type  FROM USER_TAB_COLUMNS
  ORDER BY TABLE_NAME, COLUMN_NAME;
SPOOL OFF;

It uses USER_TAB_COLUMNS to retrieve the required information about user tables.
Jatin Kumar
 
Comment and Contribute

 

 

 

 

 


(Maximum characters: 1200). You have 1200 characters left.

 

 

Sitemap