get list of table names & column names in oracle

i have a schema - myschema in an oracle database
i want to export all the table names owned by myschema along with the column names(just the names not the datatypes ) etc... (to excel or some text file )

to get the table name for myschema, i can run the sql - select table_name from user_tables;
but i want to get column names for each and every table in user_tables
is there any sql to get column names ?
i want to create a usable excel-spreadsheet with table names & column names in myschema....

thanks  a lot
ts84zsAsked:
Who is Participating?
 
Geert GOracle dbaCommented:
you could also generate csv text to paste to excel
or run the select from select over an odbc data source to your oracle

select table_name||','||column_name tab_cols
from user_tab_columns
order by table_name, column_name;

i tried this table "INFORMATION_SCHEMA.COLUMNS ", but it doesn't work for me
0
 
chaauCommented:
There is another system view for that:
SELECT TABLE_NAME, COLUMN_NAME FROM user_tab_columns

Open in new window

Also, do not forget that Oracle supports ANSI standard INFORMATION_SCHEMA catalogs:
SELECT TABLE_NAME, COLUMN_NAME
  FROM INFORMATION_SCHEMA.COLUMNS

Open in new window

0
 
PortletPaulfreelancerCommented:
too quick chaau!

no points please, investigate the other fields of that table, e.g.
SELECT
      TABLE_NAME
    , COLUMN_ID
    , COLUMN_NAME
FROM user_tab_columns
ORDER BY
      TABLE_NAME
      , COLUMN_ID

Open in new window

0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
awking00Commented:
To create excel (2010) spreadsheet -
Open excel, click on the Data tab, click on From Other Sources drop down menu arrow in the Get External Data group, select From Data Connection Wizard, highlight Other/Advanced and select Next, highlight Microsoft OLE DB Provider for Oracle and select Next, enter Server name, User Name and Password and click on OK, a list of tables/views will be provided (this can take some time depending on how many tables and views are available), select USER_TAB_COLUMNS and click Next, then click Finish, at the Import Data screen select OK (or modify as desired), re-enter the User Name, Password and Server and click OK. The data will then be loaded to your spreadsheet. Click on Sort in the Sort & Filter group, enter TABLE_NAME from the Sort by drop down menu, click on Add Level and enter COLUMN_ID from the Then by drop down menu and click OK. You can then delete all of the columns except the TABLE_NAME and COLUMN_NAME. You will then have an excel spreadsheet with all of the table and column names sorted alphabetically by table_name and in the order of the columns as they exist in those tables.
I actually think earlier versions of excel handle this more easily as they allow for sorting and filtering prior to actually importing the data. If you need to see that process, just post your version of excel.
0
 
chaauCommented:
What version of Oracle do you have. Information_schema is supported according to Oracle: http://docs.oracle.com/cd/E17952_01/refman-5.0-en/columns-table.html
0
 
Geert GOracle dbaCommented:
mysql was bought by oracle ... that's why you can see oracle in the uri for mysql references
you are on the mysql database reference site. not the oracle database reference site

this is the home for mysql 5.0 reference
http://docs.oracle.com/cd/E17952_01/refman-5.0-en
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.