我想知道是否有可能发现Oracle视图中涉及的所有表的基础主(或唯一)键列.这是一个显示我的意思的例子：
CREATE TABLE t_a ( id number(7),primary key(id) ); CREATE VIEW v_a AS SELECT * FROM t_a;
因此,通过命名约定,我知道v_a.id实际上是底层t_a表的主键列.有没有办法使用系统视图正式发现这些信息,如SYS.ALL_CONSTRAINTS,SYS.USER_CONSTRAINTS等？
N.B：
>约束不在视图上,而是在基础表上.
>我对密钥本身不感兴趣,但是在视图的列中.
您可以通过user_dependencies视图找到该信息：
sql> CREATE TABLE t_a 2 ( id number(7) 3,primary key(id) 4 ) 5 / Table created. sql> CREATE VIEW v_a AS SELECT * FROM t_a 2 / View created. sql> select c.constraint_name 2 from user_dependencies d 3,all_constraints c 4 where d.name = 'V_A' 5 and d.referenced_type = 'TABLE' 6 and d.referenced_link_name is null 7 and d.referenced_owner = c.owner 8 and d.referenced_name = c.table_name 9 and c.constraint_type = 'P' 10 / CONSTRAINT_NAME ------------------------------ SYS_C0051559 1 row selected.
问候,
抢.
编辑：对于可能的视图列名称,您可以使用此查询.请注意,您无法保证视图中存在此类列.
sql> select c.constraint_name 2,'V_' || substr(c.table_name,3) || '.' || cc.column_name possible_view_column 3 from user_dependencies d 4,all_constraints c 5,all_cons_columns cc 6 where d.name = 'V_A' 7 and d.referenced_type = 'TABLE' 8 and d.referenced_link_name is null 9 and d.referenced_owner = c.owner 10 and d.referenced_name = c.table_name 11 and c.constraint_type = 'P' 12 and c.owner = cc.owner 13 and c.constraint_name = cc.constraint_name 14 / CONSTRAINT_NAME POSSIBLE_VIEW_COLUMN ------------------------------ ------------------------------------------------------------- SYS_C0051561 V_A.ID 1 row selected.
