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

Why Trust Us

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.

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

Charlie Frank

Charlie has over a decade of experience in website administration and technology management. As the site admin, he oversees all technical aspects of running a high-traffic online platform, ensuring optimal performance, security, and user experience.
View Author
Show More