查找表的父表和子表
select zi.table_name,zi.column_name,fu.table_name,fu.column_name from user_cons_columns zi join user_constraints b on zi.constraint_name=b.constraint_name and zi.table_name=b.table_name join user_cons_columns fu on b.r_constraint_name = fu.constraint_name where b.constraint_type = 'R' and fu.table_name= '总表';
如果查询其他用户下的数据字典可以用all_cons_columns和all_constraints。
SQL> desc user_cons_columns; Name Null? Type ----------------------------------------- -------- ------------------------ OWNER NOT NULL VARCHAR2(30) CONSTRAINT_NAME NOT NULL VARCHAR2(30) TABLE_NAME NOT NULL VARCHAR2(30) COLUMN_NAME VARCHAR2(4000) POSITION NUMBER SQL> desc user_constraints; Name Null? Type ----------------------------------------- -------- ------------------------ OWNER NOT NULL VARCHAR2(30) CONSTRAINT_NAME NOT NULL VARCHAR2(30)---约束名 CONSTRAINT_TYPE VARCHAR2(1) TABLE_NAME NOT NULL VARCHAR2(30) SEARCH_CONDITION LONG R_OWNER VARCHAR2(30) R_CONSTRAINT_NAME VARCHAR2(30)---若CONSTRAINT_TYPE=R,则是外键约束关联的主表的主键名 DELETE_RULE VARCHAR2(9) STATUS VARCHAR2(8) DEFERRABLE VARCHAR2(14) DEFERRED VARCHAR2(9) VALIDATED VARCHAR2(13) GENERATED VARCHAR2(14) BAD VARCHAR2(3) RELY VARCHAR2(4) LAST_CHANGE DATE INDEX_OWNER VARCHAR2(30) INDEX_NAME VARCHAR2(30) INVALID VARCHAR2(7) VIEW_RELATED VARCHAR2(14) select z.table_name,z.column_name, z.constraint_name from user_cons_columns z join user_constraints b on b.TABLE_NAME=z.TABLE_NAME where b.R_CONSTRAINT_NAME='PK_参数表';
--父表 select b.table_name from user_constraints a join user_cons_columns b on a.r_constraint_name=b.constraint_name where a.table_name='总表';