V OnLine 7.* k sistemnoj informacii v baze mozhno dobirat'sya cherez
psevdo-tablicy SMI - SQL-interfejsom.
A vot parochka zabavnyh primerov:
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;
On pokazyvaet FOREIGN KEYS dlya vseh tablic (kotorye ih imeyut) v vide
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
Vashemu vnimaniyu predlagaetsya arhiv
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