В OnLine 7.* к системной информации в базе можно добираться через
псевдо-таблицы SMI - SQL-интерфейсом.

А вот парочка забавных примеров:

 From: Valeryan Vinogradov 

SELECT t.tabname, ".", c.colname, "->", n.tabname, ".", p.colname
 FROM
 syscolumns c,
 systables t,
 sysconstraints s,
 sysreferences r,
 systables n,
 sysindexes i,
 syscolumns p
 WHERE t.tabid = c.tabid
 AND t.tabid = s.tabid
 AND s.constrtype = 'R'
 AND i.idxname = s.idxname
 AND i.part1 = c.colno
 AND r.constrid = s.constrid
 AND p.tabid = r.ptabid
 AND p.colno = 1
 AND n.tabid = r.ptabid
 ORDER BY t.tabname;
Он показывает FOREIGN KEYS для всех таблиц (которые их имеют) в виде

TAB-NAME.COL-NAME -> FOREIGN KEY TAB-NAME.FOREIGN KEY COL-NAME


-- A vchera prishlos' sdelat' esche odin, delajuschij obratnoje:
-- referenced_table_name . referenced_column_name
-- <- referencing_table_name . referencing_column_name

SELECT n.tabname, ".", l.colname, "<-", t.tabname, ".", m.colname
 FROM
 systables n,
 sysconstraints c,
 sysindexes i,
 syscolumns l,
 sysreferences r,
 sysconstraints s,
 systables t,
 sysindexes d,
 syscolumns m
 WHERE c.tabid = n.tabid
 AND c.constrtype = "P"
 AND i.idxname = c.idxname
 AND l.tabid = n.tabid
 AND l.colno = i.part1
 AND r.primary = c.constrid
 AND s.constrid = r.constrid
 AND t.tabid = s.tabid
 AND d.idxname = s.idxname
 AND m.tabid = t.tabid
 AND m.colno = d.part1
 ORDER BY n.tabname;

    * Построитель уровней *

From: Valeryan Vinogradov Вашему вниманию предлагается архив levels.zip levels.exe creates from the file levels.txt with list of refencing and referenced tables, created with informix script file levels.sql, files levelsa.txt and levelsb.txt with lists of these tables and its levels, ascending and descending sorted by refencing tables levels. levels.txt table1 table0 table2 table1 table3 table1 levelsa.txt F1 table1 P0 table0 F2 table2 P1 table1 F2 table3 P1 table1 levelsb.txt F2 table2 P1 table1 F2 table3 P1 table1 F1 table1 P0 table0 levels.sql UNLOAD TO 'levels.txt' DELIMITER ' ' SELECT t.tabname, n.tabname FROM sysconstraints c, sysindexes i, systables n, syscolumns l, sysreferences r, sysconstraints s, sysindexes d, systables t, syscolumns m WHERE c.tabid = n.tabid AND c.constrtype = "P" AND i.idxname = c.idxname AND l.tabid = n.tabid AND l.colno = i.part1 AND r.primary = c.constrid AND s.constrid = r.constrid AND t.tabid = s.tabid AND d.idxname = s.idxname AND m.tabid = t.tabid AND m.colno = d.part1 ORDER BY 1, 2 ;

Популярность: 8, Last-modified: Sat, 25 Apr 1998 05:19:28 GMT