Q

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

Dig deeper on Using Oracle PL-SQL

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

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.

0 comments

Oldest 

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:

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close