SQL Script to List Table Columns

Recently purchased an ERP system that uses Oracle 9.2.0.6.  The system includes a data dictionary and an interface to write SQL scripts.  I would like to write 2 SQL scripts:

1.  Script that lists all of the column/field names in a single table
2.  Script that lists all of the column/field names in all of the tables.

The scripts will then be used in Crystal Reports to assist me in finding columns(fields).

Regards,
Gary
pcguru_garyAsked:
Who is Participating?
 
DrSQLConnect With a Mentor Commented:
Gary,
    If you have sqlplus as part of your ERP system, then you can "spool" the data to a file.  Also, you can search the dictionary,just like any other Oracle table.

To spool a file of all tables and all columns to which you have access:

set lines 2000
set pages 0
set heading on
set trimspool on
select * from all_tab_columns
spool tabledefs.txt
/
spool off

To search for a particular column in YOUR schema:
select table_name,column_name from user_tab_columns
where column_name like '%CREDIT%';

Here's a link to all of the views available to you: http://download.oracle.com/docs/cd/B19306_01/server.102/b14237/statviews_part.htm#REFRN002

Good luck!
0
 
ajexpertCommented:

--To list all columns for given table
SELECT * FROM USER_TAB_COLS
WHERE TABLE_NAME = '<table_name>'
--to list all columns in all tables
SELECT * FROM USER_TAB_COLS

Open in new window

0
 
mlmccCommented:
How do you plan to use them in a report?

mlmcc
0
 
pcguru_garyAuthor Commented:
ajexpert provided the script that works.  mlmcc asked an important follow up question: How do I plan to use them in a report?  Because the output from the SQL statement in Data Dictionary prints to screen only and cannot be exported, I need to create a formula in CR that can then be exported to Excel.  The table/column listing would then be easy to search on for specific column names.  

So the question is:  How do I create a CR from scratch using the script that ajexpert provided?  

Note: I increased the point value and will split them.

Regards,
Gary
0
 
DrSQLCommented:
Gary,
    It's been over a month.  Could you please update/close this question?  Thank you for using Experts Exchange.

Good luck!
0
All Courses

From novice to tech pro — start learning today.