get list of table names & column names  in oracle

Posted on 2014-08-21
Last Modified: 2014-08-27
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
Question by:ts84zs
    LVL 24

    Expert Comment

    There is another system view for that:

    Open in new window

    Also, do not forget that Oracle supports ANSI standard INFORMATION_SCHEMA catalogs:

    Open in new window

    LVL 47

    Expert Comment

    too quick chaau!

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

    Open in new window

    LVL 31

    Assisted Solution

    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.
    LVL 36

    Accepted Solution

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

    Expert Comment

    What version of Oracle do you have. Information_schema is supported according to Oracle:
    LVL 36

    Expert Comment

    by:Geert Gruwez
    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

    Featured Post

    Gigs: Get Your Project Delivered by an Expert

    Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

    Join & Write a Comment

    How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
    Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
    This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
    This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…

    745 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    18 Experts available now in Live!

    Get 1:1 Help Now