英文:
How to find a view for a certain table in Informix?
问题
我有一个拥有大量表和视图的数据库，我想找出某个表对应的视图，如果可能的话。我使用的数据库是 IBM Informix。
英文:
I have a database with loads of tables and views and I want to find out which view belongs to a certain table if this is even possible. The database I use is IBM Informix.
答案1
得分: 3
您所寻找的答案编码在系统目录中的"informix".sysdepend表中。
表1. SYSDEPEND表列描述
| 列名 | 类型 | 说明 |
|---|---|---|
btabid |
整数 | 唯一标识基表或视图的代码 |
btype |
CHAR(1) | 基本对象类型：T = 表 V = 视图 |
dtabid |
整数 | 唯一标识依赖表或视图的代码 |
dtype |
CHAR(1) | 依赖对象类型的代码；目前只实现了视图（V = 视图） |
因此，根据来自"informix".systables中的tabid值（可能标识基表或视图），您可以找出哪些其他视图依赖于它。您可能需要多次与systables进行连接。
例如：
SELECT * FROM SysDepend;
| btabid | btype | dtabid | dtype |
|---|---|---|---|
| 整数 | CHAR(1) | 整数 | CHAR(1) |
| 30 | T | 70 | V |
| 3 | T | 71 | V |
| 296 | T | 299 | V |
| 297 | T | 300 | V |
| 298 | T | 301 | V |
| 318 | T | 320 | V |
| 318 | T | 325 | V |
SELECT d.btabid, t1.owner, t1.tabname, d.btype, d.dtabid, d.dtype, t1.owner, t2.tabname
FROM "informix".SysDepend AS d
JOIN "informix".SysTables AS t1 ON d.btabid = t1.tabid
JOIN "informix".SysTables AS t2 ON d.dtabid = t2.tabid
ORDER BY t1.owner, t1.tabname, t2.owner, t2.tabname;
| btabid | owner | tabname | btype | dtabid | dtype | owner | tabname |
|---|---|---|---|---|---|---|---|
| 整数 | CHAR(32) | VARCHAR(128) | CHAR(1) | 整数 | CHAR(1) | CHAR(32) | VARCHAR(128) |
| 298 | informix | sysbldiprovided | T | 301 | V | informix | bldi_provided |
| 297 | informix | sysbldirequired | T | 300 | V | informix | bldi_required |
| 296 | informix | sysbldregistered | T | 299 | V | informix | bld_registered |
| 3 | informix | sysindices | T | 71 | V | informix | sysindexes |
| 30 | informix | sysxtdtypes | T | 70 | V | informix | sysdomains |
| 318 | sde | spatial_references | T | 320 | V | sde | spatial_ref_sys |
| 318 | sde | spatial_references | T | 325 | V | sde | spatial_references_expand |
英文:
The answer you seek is encoded in the "informix".sysdepend table in the system catalog.
Table 1. SYSDEPEND table column descriptions
| Column. | Type. | Explanation |
|---|---|---|
btabid |
INTEGER | Code uniquely identifying the base table or view |
btype |
CHAR(1) | Base object type: T = Table V = View |
dtabid |
INTEGER | Code uniquely identifying a dependent table or view |
dtype |
CHAR(1) | Code for the type of dependent object; currently, only view (V = View) is implemented |
So, given the tabid value from "informix".systables (which might identify a base table or a view), you can find out which other views depend on it. You will probably need to join with systables several times.
For example:
SELECT * FROM SysDepend;
| btabid | btype | dtabid | dtype |
|---|---|---|---|
| INTEGER | CHAR(1) | INTEGER | CHAR(1) |
| 30 | T | 70 | V |
| 3 | T | 71 | V |
| 296 | T | 299 | V |
| 297 | T | 300 | V |
| 298 | T | 301 | V |
| 318 | T | 320 | V |
| 318 | T | 325 | V |
SELECT d.btabid, t1.owner, t1.tabname, d.btype, d.dtabid, d.dtype, t1.owner, t2.tabname
FROM "informix".SysDepend AS d
JOIN "informix".SysTables AS t1 ON d.btabid = t1.tabid
JOIN "informix".SysTables AS t2 ON d.dtabid = t2.tabid
ORDER BY t1.owner, t1.tabname, t2.owner, t2.tabname;
| btabid | owner | tabname | btype | dtabid | dtype | owner | tabname |
|---|---|---|---|---|---|---|---|
| INTEGER | CHAR(32) | VARCHAR(128) | CHAR(1) | INTEGER | CHAR(1) | CHAR(32) | VARCHAR(128) |
| 298 | informix | sysbldiprovided | T | 301 | V | informix | bldi_provided |
| 297 | informix | sysbldirequired | T | 300 | V | informix | bldi_required |
| 296 | informix | sysbldregistered | T | 299 | V | informix | bld_registered |
| 3 | informix | sysindices | T | 71 | V | informix | sysindexes |
| 30 | informix | sysxtdtypes | T | 70 | V | informix | sysdomains |
| 318 | sde | spatial_references | T | 320 | V | sde | spatial_ref_sys |
| 318 | sde | spatial_references | T | 325 | V | sde | spatial_references_expand |
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论