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

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 ');