Oracle queries all user tables

Source: Internet
Author: User


The following statements are used to query all user tables in Oracle. If you are interested in Oracle queries, take a look. Www.2cto.com select * from all_tab_comments -- query tables and views of all users. select * from user_tab_comments -- query tables of the current user, view and other select * from all_col_comments -- query the column names and comments of all user tables. select * from user_col_comments -- query the column names and comments of the current user's table select * from all_tab_columns -- query the column names and other information of all users's tables (detailed but no remarks ). select * from user_tab_columns -- query the column names and other information of the user's table (detailed but no remarks ). -- generally, 1: www.2cto.com select t. table_name, t. comments from user_tab_comments t -- generally 2: select r1, r2, r3, r5from (select. table_name r1,. column_name r2,. comments r3 from user_col_comments a), (select t. table_name r4, t. comments r5 from user_tab_comments t) where r4 = r1 and above are the statements used by oracle to query all tables of a user. How to query the table names, primary key names, indexes, and foreign keys of all user tables in oracle 1. Find all indexes of the table (including index names, types, and columns ): select t. *, I. index_type from user_ind_columns t, user_indexes I where t. index_name = I. index_name and t. table_name = I. table_name and t. table_name = Name of the table to be queried 2. Search for the table's primary key (including the name and column composition): select cu. * from user_cons_columns cu, user_constraints au where cu. constraint_name = au. constraint_name and au. constraint_type = 'p' and au. table_name = table to be queried 3. uniqueness constraints of the query table (including names and columns): select column_name from user_cons_columns cu, user_constraints au where cu. constraint_name = au. constraint_name and au. constraint_type = 'U' and au. table_name = table to be queried 4. Search for the table's foreign key (including the name, referenced table name, and corresponding key name. The following is a multi-step query ): select * from user_constraints c where c. constraint_type = 'r' and c. table_name = Name of the column whose foreign key constraint is to be queried: select * from user_cons_columns cl where cl. constraint_name = foreign key name: select * from user_cons_columns cl where cl. constraint_name = foreign key reference table key name 5. query all columns of the table and their attributes select t. *, c. COMMENTS from user_tab_columns t, user_col_comments c where t. table_name = c. table_name and t. column_name = c. column_name and t. table_name = table to be queried

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.