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.

    Requires Free Membership to View

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 first published in March 2004

There are Comments. Add yours.

TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
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
Sort by: OldestNewest

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to: