你好,我是新手使用Oracle SQL Developer,请问如何查看数据库中的表结构和关系。
你好,我是新手使用Oracle SQL Developer,请问如何查看数据库中的表结构和关系。
你可以尝试
DESC <table_name>
select table_name, column_name, data_type
from all_tab_columns
where table_name = <TABLE_NAME_HERE>
and owner = '<YOUR_USER_HERE_IN_CAPITAL_LETTERS>'
如果您的表中有注释,则可以获取列的注释:
select tc.table_name, tc.column_name, tc.data_type, cc.comments
from all_col_comments cc, all_tab_columns tc
where tc.table_name = <TABLE_NAME_HERE>
and tc.owner = <OWNER_OF_TABLE_HERE>
and tc.table_name = cc.table_name
and tc.column_name = cc.column_name
and tc.owner = cc.owner
如果您以表的所有者身份登录,则可以编写以下内容:
select table_name, column_name, data_type
from user_tab_columns
where table_name = <TABLE_NAME_HERE>
或者获取带有注释的列:
select tc.table_name, tc.column_name, tc.data_type, cc.comments
from user_col_comments cc, user_tab_columns tc
where tc.table_name = '<TABLE_NAME_HERE>'
and tc.owner = '<YOUR_USER_HERE_IN_CAPITAL_LETTERS>'
and tc.table_name = cc.table_name
and tc.column_name = cc.column_name
要获取表之间的关系,请使用此查询:
select uc1.table_name
, uc1.constraint_name
, cc1.column_name
, uc2.table_name r_table_name
, uc2.constraint_name r_constraint_name
, cc2.column_name r_column_name
from all_constraints uc1
, all_constraints uc2
, all_cons_columns cc1
, all_cons_columns cc2
where 1 = 1
and uc2.constraint_type = 'R'
and uc1.constraint_name = uc2.r_constraint_name
and cc1.table_name = uc1.table_name
and cc1.constraint_name = uc1.constraint_name
and cc2.table_name = uc1.table_name
and cc2.constraint_name = uc1.constraint_name
and uc1.owner = '<YOUR_USER_HERE_IN_CAPITAL_LETTERS>'
and uc2.owner = uc1.owner
and cc1.owner = uc1.owner
and cc2.owner = uc1.owner
order by 1
以“R_”前缀开头的列表示它们是外部数据(它们代表外键)。正如您所看到的,我使用了带有“ALL_”前缀的表格,要使用类似带有“USER_”前缀的表格,请去掉“OWNER”部分。 要了解更多关于Oracle数据字典的信息,请阅读this。
1) 输入您的表名。 2) 右键点击表名并选择“打开声明”。