Оцените этот текст:Не читал10987654321
СодержаниеFine HTMLPrinted versiontxt(Word,КПК)Lib.ru html
Использование SMI tables

В 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
;
Last-modified: Sat, 25 Apr 1998 05:19:49 GMT
Оцените этот текст:Не читал10987654321