Query the information of an oracle table (table, field, constraint, index) by column name + Table Name

Source: Internet
Author: User


Query the information of an oracle table (table, field, constraint, index) by column name + Table name query constraint name SELECT * FROM USER_CONSTRAINTS WHERE table_name = upper ('org _ info ') SELECT * FROM USER_CONSTRAINTS WHERE CONSTRAINT_NAME = upper ('sys _ C0065894 '); alter table ORG_INFO add foreign key (partition) references ORG_INFO (ORG_ID); alter table ORG_INFO drop constraint select cl. constraint_name from user_cons_columns cl where table_name = upper ('org _ info') and cl. column _ Name = upper ('parent _ ORG_ID '); www.2cto.com searches for information about the query table in oracle, including the table name, field name, field type, primary key, the foreign key uniqueness constraint information. The index information query SQL is as follows, which is helpful to you: 1. query all user tables. select * from user_tables: Query all user tables. Filter by table name. perform the following operations on the letters: select * from user_tables where table_name = upper ('table name ') www.2cto.com no matter whether the table name is in upper or lower case when you create a table, after the create statement is executed, the table_name field in the corresponding user_tables table is automatically changed to upper case letters, therefore, you must use the built-in function upper to convert the string to uppercase letters for query. .
2. select * from user_indexes3 from the index of all the tables of the user, and query the index of the User table (non-clustered index ): select * from user_indexes where uniqueness = 'nonunique' 4. query the primary key of the User table (clustered index): select * from user_indexes where uniqueness = 'unique' 5. query the table index select t. *, I. index_type from user_ind_columns t, user_indexes I where t. index_name = I. index_name and t. table_name = 'node' 6. query the table's primary key select cu. * from user_cons_columns cu, user_constraints au where cu. constraint_name = au. constraint_name and au. constraint_type = 'p' AND cu. table_name = 'node' 7. 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 cu. table_name = 'node' 8. select * from user_constraints c where c. constraint_type = 'r' and c. table_name = 'staffposition': select * from user_cons_columns cl where cl. constraint_name = foreign key name
Query the column name of the key of the referenced table: select * from user_cons_columns cl where cl. constraint_name = foreign key reference table key name www.2cto.com 9. query all columns in the table and its attributes. Method 1: select * from user_tab_columns where table_name = upper ('table name'); Method 2: select cname, coltype, width from col where tname = upper ('table name ');

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.