Ocenite etot tekst:


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;

    * Postroitel' urovnej *

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
Ocenite etot tekst: