How to Query for all User Tables in Oracle Table Name Primary Key Name Indexes Foreign Keys Etc

1, the lookup table for all indexes (including the index name, type, constitute a 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 = query table

2, the lookup table’s primary key (including name, constitute column):

select cu * FROM user_cons_columns the 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 lookup table unique constraint (including name, constitute column):

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, the lookup table foreign key (including name, citing the name of the table and the corresponding key name, the following is divided into multi-step query):

select * from user_constraints c where c.constraint_type = ‘R’ and c.table_name = table to query

Discover the name of the foreign key constraint column:

select * from user_cons_columns cl where cl.constraint_name = foreign key name

Query reference table key column:

select * from user_cons_columns cl where cl.constraint_name = key name of the foreign key references the table

5, all the columns of the lookup table and its properties

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 = query table

Read Also:  How to Become A Clickbank Super Affiliate

Leave a Reply

Your email address will not be published. Required fields are marked *