Oracle database query Table Instance code

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