В 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
;
Сайт управляется системой uCoz
