How to find a view for a certain table in 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.

Solution

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