Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 7243
  • Last Modified:

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
0
ts84zs
Asked:
ts84zs
2 Solutions
 
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
 
PortletPaulCommented:
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
 
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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
Geert GruwezOracle 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:
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 GruwezOracle 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

Featured Post

Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now