对象依赖关系-SYS_DEPENDS
📄字数 1.3K
👁️阅读量 加载中...
功能描述
SYS_DEPENDS系统表用于保存和管理对象之间依赖关系信息。
字段说明
| 序号 | 字段名 | 类型 | 说明 | V11 | V12 |
|---|---|---|---|---|---|
| 0 | DB_ID | INTEGER | 库ID | √ | √ |
| 1 | OBJ_TYPE1 | INTEGER | 被依赖对象类型 | √ | √ |
| 2 | OWNER_ID1 | INTEGER | 被依赖对象模式ID | √ | √ |
| 3 | OBJ_ID1 | INTEGER | 被依赖对象的ID | √ | √ |
| 4 | OBJ_TYPE2 | INTEGER | 依赖对象类型 | √ | √ |
| 5 | OWNER_ID2 | INTEGER | 依赖对象模式ID | √ | √ |
| 6 | OBJ_ID2 | INTEGER | 对象的ID | √ | √ |
| 7 | RESERVED1 | VARCHAR | 保留字段 | √ | √ |
| 8 | RESERVED2 | VARCHAR | 保留字段 | √ | √ |
OBJ_TYPE：详见SYS_OBJECTS。
应用举例
- 外键表依赖主键表
sql
SQL> CREATE TABLE tab_dep_info1(id INT,CONSTRAINT pk1 PRIMARY KEY(id));
SQL> CREATE TABLE tab_dep_info2(id INT,CONSTRAINT pk2 PRIMARY KEY(id),CONSTRAINT fkd FOREIGN KEY(id) REFERENCES tab_dep_info1(id));
SQL> SELECT sd.*,st.table_name FROM SYS_DEPENDS sd JOIN SYS_TABLES st ON sd.db_id=st.db_id AND sd.obj_id1=st.table_id WHERE st.table_name='TAB_DEP_INFO1';
+-------+-----------+-----------+---------+-----------+-----------+---------+-----------+-----------+---------------+
| DB_ID | OBJ_TYPE1 | OWNER_ID1 | OBJ_ID1 | OBJ_TYPE2 | OWNER_ID2 | OBJ_ID2 | RESERVED1 | RESERVED2 | TABLE_NAME |
+-------+-----------+-----------+---------+-----------+-----------+---------+-----------+-----------+---------------+
| 1 | 5 | 1 | 1048577 | 5 | 1 | 1048579 | <NULL> | <NULL> | TAB_DEP_INFO1 |
+-------+-----------+-----------+---------+-----------+-----------+---------+-----------+-----------+---------------+- 视图依赖基表
sql
SQL> CREATE TABLE tab_base_view(c1 INT, c2 DOUBLE);
SQL> CREATE VIEW view1 AS SELECT c2 FROM tab_base_view;
SQL> SELECT sd.*,st.table_name FROM SYS_DEPENDS sd JOIN SYS_TABLES st ON sd.db_id=st.db_id AND sd.obj_id1=st.table_id WHERE st.table_name='tab_base_view';
+-------+-----------+-----------+---------+-----------+-----------+---------+-----------+-----------+---------------+
| DB_ID | OBJ_TYPE1 | OWNER_ID1 | OBJ_ID1 | OBJ_TYPE2 | OWNER_ID2 | OBJ_ID2 | RESERVED1 | RESERVED2 | TABLE_NAME |
+-------+-----------+-----------+---------+-----------+-----------+---------+-----------+-----------+---------------+
| 1 | 5 | 1 | 1048581 | 9 | 1 | 1048582 | <NULL> | <NULL> | TAB_BASE_VIEW |
+-------+-----------+-----------+---------+-----------+-----------+---------+-----------+-----------+---------------+- 表依赖存储函数
sql
SQL> CREATE FUNCTION dep_func RETURN VARCHAR AS
BEGIN
RETURN 'TOM';
END;
/
SQL> CREATE TABLE tab_dep_func(c1 VARCHAR DEFAULT dep_func);
SQL> SELECT sd.*,st.table_name FROM SYS_DEPENDS sd JOIN SYS_TABLES st ON sd.db_id=st.db_id AND sd.obj_id2=st.table_id WHERE st.table_name='tab_dep_func';
+-------+-----------+-----------+---------+-----------+-----------+---------+-----------+-----------+--------------+
| DB_ID | OBJ_TYPE1 | OWNER_ID1 | OBJ_ID1 | OBJ_TYPE2 | OWNER_ID2 | OBJ_ID2 | RESERVED1 | RESERVED2 | TABLE_NAME |
+-------+-----------+-----------+---------+-----------+-----------+---------+-----------+-----------+--------------+
| 1 | 7 | 1 | 1048583 | 5 | 1 | 1048584 | <NULL> | <NULL> | TAB_DEP_FUNC |
+-------+-----------+-----------+---------+-----------+-----------+---------+-----------+-----------+--------------+