Basic information of the Oracle database query table

Because the data structure design of some modules in the Oracle project is not strictly in accordance with certain specifications, the data structure can only be queried from the database. The information to be queried is as follows: Field

Because the data structure design of some modules in the Oracle project is not strictly in accordance with certain specifications, the data structure can only be queried from the database. The information to be queried is as follows: Field

Because the data structure design of some modules in the Oracle project is not strictly in accordance with certain specifications, the data structure can only be queried from the database. The information to be queried is as follows: field name, data type, whether it is null, default value, primary key, foreign key, and so on.

I searched for the above information on the Internet and summarized it as follows:

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, reference 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 = table to be queried

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 =