Oracle queries table information (indexes, foreign keys, columns, etc ..) Post

Through searching, I summarized the information about the query table in Oracle, including the table name, field name, field type, primary key, foreign key uniqueness constraint information. The index information query SQL is as follows, hope to help you:

1. query all user tables.
Select * From user_tables: all user tables can be queried.

Select owner, table_name from all_tables; query all tables, including other user tables

To filter table names, you need to process the following letters:

Select * From user_tables where table_name = upper ('table name ')

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 upper function to convert the string to uppercase letters for query. Otherwise, even if the table creation statement passes the preceding query statement, the corresponding records cannot be queried.
2. query the indexes of all your tables.
Select * From user_indexes
3. query the index of a user table (non-clustered index ):
Select * From user_indexes where uniqueness = 'nonunique'
4. query the primary key of a 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. query the uniqueness constraints of a 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. Search for the table's Foreign keys
Select * From user_constraints c Where C. constraint_type = 'r' and C. table_name = 'staffposition'
Query the names of Columns with foreign key constraints:
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
9. query all the columns and their attributes of the table.
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'); 10. query the existing processes and functions of a user: Select object_name, created, status from user_objects where lower (object_type) in ('Procedure ', 'function'); 11. select * From role_tab_privs;