Query basic table field information, primary key, and foreign key in Oracle

Because the data structure design of some modules of the 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'
Iii. query table foreign keys

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 Region 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'

With the preceding SQL statements, combined with the program (such as java + jxl), you can output the Excel report as shown in.