First, the query table basic information
Select
Utc.column_name,utc.data_type,utc.data_length,utc.data_precision, Utc.data_scale,utc.nullable,utc.data_default, Ucc.comments
From
User_tab_columns utc,user_col_comments UCC
where
Utc.table_name = ucc.table_name and Utc.column_name = ucc.column_name and utc.table_name = ' Onlinexls '
ORDER BY
column_id
Note: The meaning of the order by column_id is to make the results appear as they are in the design data structure.
Second, query table primary key
Select
Col.column_name
From
User_constraints Con,user_cons_columns Col
where
Con.constraint_name=col.constraint_name and Con.constraint_type= ' P ' and col.table_name= ' Onlinexls ' Three, query table foreign key
Select
Distinct (ucc.column_name) Column_name,rela.table_name,rela.column_name column_name1
From
User_constraints uc,user_cons_columns UCC, (select T2.table_name,t2.column_name,t1.r_constraint_name from User_ Constraints T1,user_cons_columns T2 where t1.r_constraint_name=t2.constraint_name and t1.table_name= ' Onlinexls ') rela
where
Uc.constraint_name=ucc.constraint_name and Uc.r_constraint_name=rela.r_constraint_name and Uc.table_name= ' Onlinexls '
1, look up all the indexes of the table (including index name, type, constitute 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.tabl E_name and t.table_name = table to query
2, lookup table's primary key (including name, constituent column) Table name:
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 ';
Query table primary KEY only
Select column_name from User_cons_columns where constraint_name into (select Constraint_name from user_constraints where tab Le_name =upper (' table name ') and constraint_type= ' P ');
3. Lookup table Uniqueness constraint (including name, constituent column):
Select column_name from User_cons_columns cu, user_constraints au where cu.constraint_name = Au.constraint_name and Au.con Straint_type = ' U ' and au.table_name = table to query
4. Find the foreign key of the table (including name, table name of reference table and corresponding key name, below is divided into multi-step query):
SELECT * from user_constraints c where c.constraint_type = ' R ' and c.table_name = to query
Query the column name of a FOREIGN KEY constraint:
SELECT * from User_cons_columns cl where cl.constraint_name = FOREIGN key Name
Column name of the query referencing the key of the table:
SELECT * from User_cons_columns cl where cl.constraint_name = foreign key reference table key name
5, all the columns of the query 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 = table to query