Использование 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.html  with  list of
refencing and referenced tables, created with  informix  script
file  levels.sql,  files levelsa.html and levelsb.html with lists
of these tables and its levels, ascending and descending sorted
by refencing tables levels.

      levels.html
table1 table0
table2 table1
table3 table1

      levelsa.html
F1 table1 P0 table0
F2 table2 P1 table1
F2 table3 P1 table1

      levelsb.html
F2 table2 P1 table1
F2 table3 P1 table1
F1 table1 P0 table0


      levels.sql
UNLOAD TO
 'levels.html'
 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










Проект Либмонстра, партнеры БЦБ - Украинская цифровая библиотека и Либмонстр Россия

https://database.library.by