Problem solve Get help with specific problems with your technologies, process and projects.

Command to print database tables

Is there a command that I can use in Oracle to print all the tables in a database, including two or three rows?...

I'm trying to create a data dictionary and want to have examples of the data each table contains. Not that I know of... But you can write your own script to do something like that. There are designer tools that can reverse engineer schemas.

If you want to print all tables for all schemas you need to use DBA_IND_COLUMNS and DBA_TABLES, but for a particular schema you can use user_ind_columns and user_tables logging on as the schema owner.

You can run the following script for each table out there. Alternatively, you can have a PL/SQL procedure or SQL*PLUS script (Anonymous PL/SQL block) to select TABLE_NAME from USER_TABLES and use that to run the following script to provide you with table definition. You can get fancy and modify the Data Type selection to decode it to show data length for only VARCHAR2, and CHAR data types.

 SELECT CASE WHEN rownum < 2 THEN RPAD(TABLE_NAME, 30, ' ') || CHR(10) || RPAD('Column Name', 30, ' ') || CHR (32) || CHR(32) || LPAD('NULL?',8, ' ') || CHR(32) || 'Data type' || CHR(10) || RPAD('______', 30, '_') || CHR(32) || CHR(32) || LPAD('_____', 8, '_') || CHR(32) || LPAD('_____',30, '_') || CHR(10) || RPAD(COLUMN_NAME, 30, ' ') || CHR(32) || CHR(32) || DECODE(NULLABLE, 'Y', 'NOT NULL'|| CHR(32), LPAD(' ', 8, ' ') || CHR(32) ) || data_type || '(' || data_length || ')' ELSE RPAD(COLUMN_NAME, 30, ' ') || CHR(32) || CHR(32) || DECODE (NULLABLE, 'Y', 'NOT NULL'|| CHR(32), LPAD(' ', 8, ' ') || CHR(32) ) || data_type || '(' || data_length || ')' END FROM user_tab_columns WHERE table_name = '&TABLE_NAME' ORDER BY column_id

Now to dynamically generate SQL script that gives you 2 to 3 rows..

 SELECT 'SELECT * FROM ' || table_name || ' where rownum < 3 ; -- Or 4 if you want ' FROM USER_TABLES;

And spool the result to a file that can be run to provide you with the rows. You can incorporate the SQL statements to customize your needs.

This was last published in March 2004

Dig Deeper on Using Oracle PL-SQL

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Please create a username to comment.