Useful SQL Tricks

At least, for Informix's warped version of SQL. I needed to find all the tables that referenced a particular row in an existing table, because the row I created in one table could not be deleted until all the references went away. The following SQL will give you a list of all tables that have a column named 'ofndr_num':


SELECT TRIM(t.tabname) AS table
FROM "informix".systables AS t, "informix".syscolumns AS c
WHERE t.tabid = c.tabid AND t.tabtype = 'T' AND c.colname = 'ofndr_num' AND t.tabid >= 100 ORDER BY t.tabname, c.colno ;