13 Replies Latest reply: Aug 10, 2011 1:52 PM by 880368 RSS

    list all tables and fields

    880368
      Is there a query that will retreive all tables and corresponding columns in oracle. I do not want any system tables included in the query. Thanks,
        • 1. Re: list all tables and fields
          sb92075
          877365 wrote:
          Is there a query that will retreive all tables and corresponding columns in oracle. I do not want any system tables included in the query. Thanks,
          query DBA_TAB_COLUMNS
          • 2. Re: list all tables and fields
            Frank Kulash
            Hi,
            877365 wrote:
            Is there a query that will retreive all tables and corresponding columns in oracle.
            You can do something like this:
            SELECT       owner
            ,       table_name
            ,       column_name
            FROM       dba_tab_columns
            WHERE        owner     NOT IN ('SYS', 'SYSTEM')
            ORDER BY  owner
            ,            table_name
            ,       column_id
            ;
            I do not want any system tables included in the query.
            What are "system tables"? The query above won't include anything in the SYS or SYSTEM schemas. You can add other schemas, such as 'FLOWS_030000', 'CTXSYS', and 'SCOTT', to the list if you like.
            • 3. Re: list all tables and fields
              Jhon
              it show error-

              table does not exist
              • 4. Re: list all tables and fields
                sb92075
                Sh123 wrote:
                it show error-

                table does not exist
                use COPY & PASTE so we can see what you do & how Oracle really responds
                • 5. Re: list all tables and fields
                  Mahir M. Quluzade
                  Hi,

                  then try this please,
                   
                  SELECT table_name,
                    column_name
                  FROM user_tab_cols
                  ORDER BY table_name,
                    column_id; 
                  
                   
                  • 6. Re: list all tables and fields
                    Jhon
                    ok,thanks
                    • 7. Re: list all tables and fields
                      880368
                      I just read user_tabs_cols is for user owned tables and not the entire database. I will have to figure out in the dba_tabs_columns query as to eliminate system tables and also it should show just tables and not views etc. I do not have the Oracle set up and therefore cannot run the queries to check for the results. Thanks everyone for helping out.
                      • 8. Re: list all tables and fields
                        bpat
                        You can also use all_tab_cols
                        • 9. Re: list all tables and fields
                          Solomon Yakobson
                          751129 wrote:
                          You can also use all_tab_cols
                          It will show tables/views user has access to, not all tables in the entire database. To get tables & columns in the entire database you must use DBA_TAB_COLS/DBA_TAB_COLUMNS.

                          SY.
                          • 10. Re: list all tables and fields
                            880368
                            I seem to agree with your statement. So there are still two parts a) The dba_tabs_Columns has data for both tables/ views... I want data for only tables and not views etc. b) The dba_Tabs_columns returns user tables info. Additionally does it return system tables info also. I do not want any system tables information. Thanks,
                            • 11. Re: list all tables and fields
                              sb92075
                              877365 wrote:
                              I seem to agree with your statement. So there are still two parts a) The dba_tabs_Columns has data for both tables/ views... I want data for only tables and not views etc. b) The dba_Tabs_columns returns user tables info. Additionally does it return system tables info also.
                              I do not want any system tables information. Thanks,
                              Enumerate exactly which schemas comprise what you consider to be "system" tables?

                              Edited by: sb92075 on Aug 10, 2011 10:04 AM
                              • 12. Re: list all tables and fields
                                Frank Kulash
                                877365 wrote:
                                I seem to agree with your statement. So there are still two parts a) The dba_tabs_Columns has data for both tables/ views... I want data for only tables and not views etc.
                                There's nothing in dba_tab_columns that distinguisheds views from tables. dba_tables has one row per table, and nothing for views. You can join to dba_tables to get information about tables only:
                                SELECT       c.owner
                                ,       c.table_name
                                ,       c.column_name
                                FROM       dba_tab_columns  c
                                JOIN       dba_tables        t  ON  c.owner     = t.owner
                                                       AND c.table_name     = t.table_name
                                WHERE        c.owner     NOT IN ('SYS', 'SYSTEM')
                                ORDER BY  c.owner
                                ,            c.table_name
                                ,       c.column_id
                                ;
                                Do you like jargon? The query above is an example of a Semi-Join , where we join to a table (dba_tables), but don't use that table in the SELECT clause, or anywhere else in the query.
                                b) The dba_Tabs_columns returns user tables info. Additionally does it return system tables info also. I do not want any system tables information. Thanks,
                                How to do that depends on what you mean by "system tables". If you mean tables in certain schemas, then you can put the schema names in the IN-list above, in addition to, or instead of, 'SYS' and 'SYSTEM'.
                                • 13. Re: list all tables and fields
                                  880368
                                  I was looking for 'SYS' and 'SYSTEM' when I meant system related table. I believe this should suffice my requirement. Thanks,