Informix SQL - List All Fields & Tables

Informix SQL - List all fields & tables

Using the preferred JOIN notation:

SELECT TRIM(t.tabname) || '.' || TRIM(c.colname) AS table_dot_column
  FROM "informix".systables  AS t
  JOIN "informix".syscolumns AS c ON t.tabid = c.tabid
 WHERE t.tabtype = 'T'
   AND t.tabid >= 100
 ORDER BY t.tabname, c.colno;

or the old-fashioned join-in-where-clause notation:

SELECT TRIM(t.tabname) || '.' || TRIM(c.colname) AS table_dot_column
  FROM "informix".systables AS t, "informix".syscolumns AS c
 WHERE t.tabid = c.tabid
   AND t.tabtype = 'T'
   AND t.tabid >= 100
 ORDER BY t.tabname, c.colno;

Assuming you are using a sufficiently recent version of IDS, you can order by columns not cited in the select-list. If you get complaints, add the ordering columns to the select list.

The join criterion is obvious; the tabtype = 'T' lists only tables, not views, synonyms and other such items listed in systables; the tabid >= 100 only lists tables created explicitly in the database, not the system catalog.

This does not include the type information - if you want that, you have to do a bit more work. You will find a file $INFORMIXDIR/etc/xpg4_is.sql that contains a crude approximation to an old version of the XPG4 (X/Open standard) Information Schema (hence the file name). In there, there are functions etc to decode type information from syscolumns.coltype and syscolumns.collength into recognizable strings. However, I strongly suspect it does not handle DISTINCT types, nor other user-defined types. I'll be delighted to be proved wrong, but... If you add the relevant parts of that file to your database, you should then be able to get the type information too.

Also note that all the INFO commands in ISQL and DB-Access are simulated in the front-end, not executed in the IDS server. Basically, the programs take the request and convert it into a more complex SQL statement. See the code in the file sqlinfo.ec that is part of SQLCMD (available from the IIUG Software Archive) for how my SQLCMD program handles INFO statements. (Note: the INFO output of SQLCMD is formatted differently from the INFO output of ISQL and DB-Access.)

How to get a list of tables for a specific database when connected using sysmaster user in Informix?

Try this :

   select tn.partnum, tn.dbsname, tn.owner, tn.tabname
         , pt.lockid, pt.flags, pt.flags2
         , pt.rowsize, pt.ncols, pt.pagesize
         , dbinfo('utc_to_datetime',pt.created) as created
         , pt.nrows, pt.nptotal, pt.npused, pt.npdata
         , pt.nrows * pt.rowsize as tamanho_bytes
         , (pt.nptotal * pt.pagesize) /1024 as total_kb
         , (pt.npused  * pt.pagesize) /1024 as used_kb
         , (pt.npdata  * pt.pagesize) /1024 as data_kb
         , ((pt.nptotal - pt.npused) * pt.pagesize) /1024 as free_kb
         , pt.nextns as extents
         , pt.ninserts
         , pt.nupdates
         , pt.ndeletes
         , coalesce(pt.serialv,pt.cur_serial8,pt.cur_bigserial)::bigint serial
         , pta_oldvers
         , pta_newvers
    from sysmaster:sysptnhdr pt
    left join sysmaster:systabnames tn on tn.partnum = pt.partnum

TBLspaces are listed too and fragmented tables are showed separated where you can group by lockid column (~partnum).

Search all columns in Informix table for a value

There isn't a built-in way to do it. You'd have to do:

SELECT * FROM Table WHERE Column1 = <your-value>
UNION
SELECT * FROM Table WHERE Column2 = <your-value>
UNION
...

---

Automatic query generation

Is there a programmatic way to generate the mass UNION-SELECT statements? Some of my target tables have numerous columns.

What's your weapon of choice? What is that database name, the table name, and the value?
My weapon of choice is SQLCMD, the program is available from the IIUG Software Archive and not Microsoft's johnny-come-lately creation of the same name.

dbname=stores
table=customers
value=Raymond

sqlcmd -D'\n' -d $dbname -e \
    "select 'SELECT * FROM $table WHERE ', c.colname,
            '::VARCHAR(64) = ''$value''', 'UNION'
       from informix.syscolumns as c
       join informix.systables as t on t.tabid = c.tabid
      where t.tabname = '$table' order by colno" |
sed '$d'

The output query keywords are in upper-case; the meta-query that generates it is in lower-case. The meta-query is a joining SysTables with SysColumns. The "-D '\n'" option says "the field delimiter is a newline" (as well as the record delimiter). I make sure the UNION is on a line of its own to make it easy to remove the last one. I cast the columns to VARCHAR(64) so that they can be compared with the string regardless of source type - almost regardless of source type, because BYTE, TEXT, BLOB and CLOB columns won't convert. If you want a LIKE '%Raymond%' predicate, adapt the query accordingly.

You can achieve similar results with Perl and DBI and DBD::Informix.

Getting the output with DB-Access would be messy; you'd probably use the built-in 'OUTPUT TO "/dev/stdout" WITHOUT HEADINGS select ...' where the ... is the remainder of the query shown above. You then have to get rid of the last two lines of the output, which is much harder than just the last one. Probably the simplest is brute force - run two copies of sed '$d' on the output, but the solution does not scale well. Failing that, save the output in a file and used ed or ex to edit it.

Example output:

SELECT * FROM customer WHERE
customer_num
::VARCHAR(64) = 'Raymond'
UNION
SELECT * FROM customer WHERE
fname
::VARCHAR(64) = 'Raymond'
UNION
SELECT * FROM customer WHERE
lname
::VARCHAR(64) = 'Raymond'
UNION
SELECT * FROM customer WHERE
company
::VARCHAR(64) = 'Raymond'
UNION
SELECT * FROM customer WHERE
address1
::VARCHAR(64) = 'Raymond'
UNION
SELECT * FROM customer WHERE
address2
::VARCHAR(64) = 'Raymond'
UNION
SELECT * FROM customer WHERE
city
::VARCHAR(64) = 'Raymond'
UNION
SELECT * FROM customer WHERE
state
::VARCHAR(64) = 'Raymond'
UNION
SELECT * FROM customer WHERE
zipcode
::VARCHAR(64) = 'Raymond'
UNION
SELECT * FROM customer WHERE
phone
::VARCHAR(64) = 'Raymond'

When the output above is fed into a second copy of SQLCMD, it produced the output:

 105|Raymond|Vector|Los Altos Sports|1899 La Loma Drive||Los Altos|CA|94022|415-776-3249

How do I list all functions in Informix?

Using the sysprocedures catalog table you can filter for non procedureSPLs:

SELECT
    *
FROM
    sysprocedures
WHERE
    isproc = 'f';

SYSPROCEDURES

Review information about user-defined routines

However, I am not aware of an exact way to determine if a function is user defined.

You could look to the owner of the function, but if it was created with the user informix then it can no longer be distinguished from the system functions.

The other catalog tables related to functions also do not seem to contain enough information to help:

SYSPROCAUTH
SYSPROCBODY
SYSPROCCOLUMNS
SYSPROCPLAN
SYSROUTINELANGS

Reading a bit more into the documentation, it seems that a lower case value mode column indicates a protected (system) routine . So the routines that are of interest are the ones with modes O, R, D and T (all uppercase).

SELECT
    *
FROM
    sysprocedures
WHERE
    isproc = 'f'
    AND mode IN ('O', 'R', 'D', 'T');

However, this does not guarantee that the routine is user created. In the sysadmin there are routines marked with mode O that were created by the system.

Also if you, for example, in Informix version 12.10.FC10, use the a regex routine, the system automatically registers the regex datablade and the corresponding support routines are created with mode O .

EXECUTE FUNCTION regex_match ('Regex module' , '[Mm]odule|DataBlade');

SELECT * FROM sysprocedures WHERE procname= 'regex_match';

procname        regex_match
owner           informix
procid          568
mode            O
retsize         200
symsize         813
datasize        0
codesize        0
numargs         4
isproc          f
specificname
externalname    $INFORMIXDIR/extend/ifxregex.1.00/ifxregex.bld(ifxregex_match_c
                har)
paramstyle      I
langid          1
paramtypes      lvarchar,lvarchar,integer,integer
variant         f
client          f
handlesnulls    t
iterator        f
percallcost     1000
commutator
negator
selfunc
internal        f
class
stack
parallelizable  t
costfunc
selconst        0.00
collation       en_US.819
procflags       0

So, system created, but with mode O.

How to search informix database for a column

SELECT tabname, colno, colname  
FROM systables a, syscolumns b 
WHERE a.tabid = b.tabid 
and colname = "cust_nbr"
ORDER BY colno; 

I found this code in the same place and added the extra restriant with colname = cust_nbr.

This seems to have worked for me. i will verify it, but all signs look like it worked.

i found that in the Using the Informix Catalogs mentioned in the other post

Informix: How to get the table contents and column names using dbaccess?

None of the standard Informix tools put the column names at the top of the output as you want.

The program SQLCMD (not the Microsoft newcomer - the original one, available from the IIUG Software Archive) has the ability to do that; use the -H option for the column headings (and -T to get the column types).

sqlcmd -U -d my_database -t my_table -HT -o my_table.txt
sqlunload -d my_database -t my_table -HT -o my_table.txt

SQLCMD also can do CSV output if that's what you need (but — bug — it doesn't format the column names or column types lines correctly).

Selecting all rows from Informix table containing some null columns

After a balked attempt at installing DBD::Informix I came back around to this and found that for some reason enabling LongTruncOk on the database handle did allow all rows including those with null columns to be selected. I don't imagine this is the root of the issue but it worked here.

However, this solution seems to have collided with an unrelated tweak to locales to support non-ascii characters. I added DB_LOCALE=en_us.utf8 and CLIENT_LOCALE=en_us.utf8 to my connection string to prevent selects from similarly breaking when encountering non-ascii characters (i.e., in a result set of say 500 where the 300th row had a non-ascii character the trailing 200 rows would not be returned). With locales set this way as well as LongTruncOk enabled on the dbh all rows are being returned (without the NVL hack), but null columns have bytes added to them from previous rows, and not in any pattern that is obvious to me. When I leave the locale settings off of the connection string and set LongTruncOk, rows with null columns are selected correctly but rows with utf characters break.

So if you don't have a charset issue perhaps just LongTruncOk would work for you. For my purposes I have had to continue using the NVL workaround for nulls and specify the locales for characters.

---

Related Topics