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;


It uses USER_TAB_COLUMNS to retrieve the required information about user tables.

See also  5 Tips for Choosing the Right Medical Software for Your Practice
devxblackblue

About Our Editorial Process

At DevX, we’re dedicated to tech entrepreneurship. Our team closely follows industry shifts, new products, AI breakthroughs, technology trends, and funding announcements. Articles undergo thorough editing to ensure accuracy and clarity, reflecting DevX’s style and supporting entrepreneurs in the tech sphere.

See our full editorial policy.

About Our Journalist