View all tables, user tables, column names, primary keys, and foreign keys in Oracle
View all tables in Oracle:
select * from tab/dba_tables/dba_objects/cat;
Look at the table created by the user:
select table_name from user_tables; //The table of the current user
select table_name from all_tables; // tables for all users
select table_name from dba_tables; //Including system table
select * from user_indexes //You can query all user table indexes
Check all users' tables in all_tables
The primary key name and foreign key are all_constraints
Indexed in all_indexes
But the primary key will also become an index, so the primary key will also be in all_indexes.
The specific fields required can be DESC these views. If you log in to dba, you can change all to dba.
1. Look up all indexes of the table (including index name, type, and column):
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 = table to query
2. Look up the primary key of the table (including name and form column):
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 query
3. The uniqueness constraints of the lookup table (including names and constitute 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 query
4. Look up the foreign keys of the table (including names, table names of the reference tables and corresponding key names, the following is divided into multiple steps):
select * from user_constraints c where c.constraint_type = 'R' and c.table_name = table to query
Query the column names of foreign key constraints:
select * from user_cons_columns cl where cl.constraint_name = foreign key name
Query the column name of the keys that reference the table:
select * from user_cons_columns cl where cl.constraint_name = key name of the foreign key reference table
5. All columns and their properties of the query table
select t.*, 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 query