devxlogo

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

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 OFFSET LINESIZE 400SET PAGESIZE 1000BREAK ON TABLE_NAME SKIP 3SPOOL 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;

size=3>
It uses USER_TAB_COLUMNS to retrieve the required information about user tables.

devx-admin

Share the Post: