ORACLE Database Query table instance code

1. query basic table information & nbsp; select & nbsp; utc. column_name, utc. data_type, utc. data_length, utc. data_precision, utc. data_Scale, utc. nullable, utc. data_default, ucc. comments & nbsp; & nbsp

I. query basic table 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

Column_id

Note: order by column_id indicates that the result is displayed in the order of data structure design.

Ii. query the table's 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' 3. query the 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. Search for all indexes of a table (including index names, types, and columns ):

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 be queried

2. Search for the table's primary key (including the name and column composition). The table name is written as follows:

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 be queried ';

Only query Table Primary keys

Select column_name from user_cons_columns wherE CONSTRAINT_NAME in (select CONSTRAINT_NAME from user_constraints where table_name = upper ('table name') and CONSTRAINT_TYPE = 'P ');

3. 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 au. constraint_type = 'U' and au. table_name = table to be queried

4. Search for the table's foreign key (including the name, referenced table name, and corresponding key name. The following is a multi-step query ):

Select * from user_constraints c where c. constraint_type = 'r' and c. table_name =

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

5. query all the columns and their attributes of the table.

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 be queried