Re: Posting from the Informix-list

Obnoxio The Clown wrote: > > From: Jonathan Leffler <jleffler@earthlink.net> > > > >Andrew Ford wrote: > > > Can anyone help me with a sysmaster tables question? > > > > > > Given a table name and a list of column names I would > > > like to know if there is an index on that table with > > > those columns, and if so what is the name of that index? > > > >This is a non-trivial question in general; I don't have a fully > >worked out solution to offer, but maybe these pointers will be > >helpful. > > > >First, I'd expect to be working with sysindexes, syscolumns and > >perhaps systables from the database's system catalogue, not using > >the sysmaster database at all. > > Ahah! But let's say you have many databases and you want to know all the > indexes in all the databases? (Not that I have the solution, I'm a bit lazy > today, but I also have the requirement, if anyone is feeling more energetic > than I... :-) I'll go along with Jonathan's observations, and I'm not feeling too energetic myself, but this might get you started: OUTPUT TO PIPE "dbaccess sysmaster 2>/dev/null" WITHOUT HEADINGS SELECT "SELECT '" || TRIM(name) || "' database, tabname, idxname" _1, "FROM " || TRIM(name) || ":systables t, " || TRIM(name) || ":sysindexes i" _2, "WHERE t.tabid = i.tabid" _3, "AND t.tabid > 99;" _4 FROM sysmaster:sysdatabases WHERE name NOT MATCHES "sys*" AND is_logging = 1 With some provisos: 1) This works for logged database. If you want non logged databases, then change the sysmaster database in the PIPE to a non logged database and look for is_logging = 0. 2) Be careful using the output of dbaccess as the input to another dbaccess command, as dbaccess has it's own rules for where to throw in a <CR>. If all else fails, use UNLOAD TO <filename> DELIMITER ";" 3) Obviously if you have databases named sys* or an onpload database then you may want to change the filter a bit. :-) Cheers, -- Mark. +----------------------------------------------------------+-----------+ | Mark D. Stock http://www.informix.com |//////// /| | mailto:mdstock@mydas.freeserve.co.uk |///// / //| | http://www.iiug.org +-----------------------------------+//// / ///| | |What year 2000 bug? year 2000 bug? |/// / ////| | |year 2000 bug? year 2000 bug? year |// / /////| | |2000 bug? year 2000 bug? year 1900 |/ ////////| +----------------------+-----------------------------------+-----------+