trying to identify table

Hello folks IBM Informix Dynamic Server Version 11.50.FC6 HP-UX wuasp132 B.11.23 U 9000/800 3 I am trying to identify a table in our database with the name 181_1029 . It is the most heavily used table in the database. I have done a dbschema on the whole dtabase but this name does not appera anywhere. How can I find out what it is ? I am guessing it is an index strange name caused by unamed contraint

It sounds exactly like a constraint index. Dbschema does not show the names of constraint indexes. You can try searching sysindexes where idxname = ' 181_1029' in each database. Don't forget the leading space charater. If you have my dbschema replacement utility, myschema, it DOES print out the definitions of constraint indexes replacing the leading space with a character representing the constraint type that it supports (ie 'P'-primary key, 'U' - unique, 'R' - foreign key or reference). Art Art S. Kagel Advanced DataTools (www.advancedatatools.com) Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Advanced DataTools, the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Wed, Sep 14, 2011 at 6:47 PM, KARL OLIVER <karl.oliver@maf.govt.nz>wrote: > Hello folks > > IBM Informix Dynamic Server Version 11.50.FC6 > HP-UX wuasp132 B.11.23 U 9000/800 3 > I am trying to identify a table in our database with the name > 181_1029 . It is the most heavily used table in the database. > I have done a dbschema on the whole dtabase but this name does not appera > anywhere. > How can I find out what it is ? > I am guessing it is an index strange name caused by unamed contraint > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --000e0cd3a1d23ffecd04aceed89a

Hello. If you want to check tablename using indexname, check this query connected to your database: select b.tabname from sysindices a, systables b where a.tabid = b.tabid and idxname matches '*181_1029' Regards. Em 14/09/2011 19:09, Art Kagel escreveu: > It sounds exactly like a constraint index. Dbschema does not show the names > of constraint indexes. You can try searching sysindexes where idxname = ' > 181_1029' in each database. Don't forget the leading space charater. If > you have my dbschema replacement utility, myschema, it DOES print out the > definitions of constraint indexes replacing the leading space with a > character representing the constraint type that it supports (ie 'P'-primary > key, 'U' - unique, 'R' - foreign key or reference). > > Art > > Art S. Kagel > Advanced DataTools (www.advancedatatools.com) > Blog: http://informix-myview.blogspot.com/ > > Disclaimer: Please keep in mind that my own opinions are my own opinions and > do not reflect on my employer, Advanced DataTools, the IIUG, nor any other > organization with which I am associated either explicitly, implicitly, or by > inference. Neither do those opinions reflect those of other individuals > affiliated with any entity with which I am affiliated nor those of the > entities themselves. > > On Wed, Sep 14, 2011 at 6:47 PM, KARL OLIVER<karl.oliver@maf.govt.nz>wrote: > >> Hello folks >> >> IBM Informix Dynamic Server Version 11.50.FC6 >> HP-UX wuasp132 B.11.23 U 9000/800 3 >> I am trying to identify a table in our database with the name >> 181_1029 . It is the most heavily used table in the database. >> I have done a dbschema on the whole dtabase but this name does not appera >> anywhere. >> How can I find out what it is ? >> I am guessing it is an index strange name caused by unamed contraint >> >> >> >> > ******************************************************************************* >> Forum Note: Use "Reply" to post a response in the discussion forum. >> >> > --000e0cd3a1d23ffecd04aceed89a > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > -- Alexandre Marini Tecnologia da Informação - DBA SEFAZ-MS / SGI-UGSR / Sistemas IBM-Informix <Cert-Info-Mgmt_color.jpg> IBM Certified System Administrator - Informix Dynamic Server V10 / V11 / V11.70 IBM Information Management Informix Technical Professional v3

select b.tabname from sysindices a,systables b where a.tabid = b.tabid and idxname matches '*181_1029' returned no rows

Hi, maybe you find table SYSCONSTRAINTS in your database in question useful: echo "select * from sysconstraints"|dbaccess your_db (...) constrid 24 constrname n109_24 owner informix tabid 109 constrtype N idxname collation en_US.819 (...) You can try something like, if your 'table' looks like 181_1029: select * from sysconstraints where tabid = '181' and constrid::varchar(10) like '%1029%' or select * from sysconstraints where tabid = '181' and constrid::varchar(10)matches '*1029' (the ::varchar(10) is to make a cast to a varchar data type, otherwise you cannot cannot use LIKE or MATCHES . Cheers.

Thanks GERARDO The table SYSCONSTRAINT was what I wanted. Using this with sql from Alexandre I get what I want select b.tabname from SYSCONSTRAINTS a, systables b where a.tabid = b.tabid and idxname matches '*181_1029' tabname uorcat_spc_lk Any developers out there reading this . It is a good idea to name constraints. Another thing that intrigues me is why the information like this is not in one table?

I know this has nothing to do but does anyone know how to get IDS Forum prefrence settings to stay. No matter how many time I set Posted within the last 1 Month it reverts back to default of 6 months. When I try to change my name to lower has that does not work either i have cookies enabled