asked on

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

ajexpert

--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

Mike McCracken

How do you plan to use them in a report?

mlmcc

pcguru_gary

ASKER

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

ASKER CERTIFIED SOLUTION

DrSQL - Scott Anderson

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

DrSQL - Scott Anderson

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

Good luck!