Informix – How to Search for a Table Across Catalogs

informix

I would like find schema in which a table exists in Informix database.

I know that we can use below query. But that searches only with a schema. 

select tabname from "informix".systables where tabid > 99 ;

Best Answer

The chances are that you can find what you want by running a query against sysmaster:'informix'.systabnames – which is a table in the sysmaster database.

SELECT * FROM sysmaster:'informix'.systabnames

Sample output (rather carefully selected rows):

partnum  dbsname    owner     tabname                       collate      dbsnum
INTEGER  CHAR(128)  CHAR(32)  CHAR(128)                     CHAR(32)     INTEGER
1048586  sysmaster  informix  sysusers                      en_US.819    1
1048641  sysmaster  informix  sysextdfiles                  en_US.819    1
1048648  sysuser    informix  sysattrtypes                  en_US.819    1
1048673  sysuser    informix  syssecpolicycomponents        en_US.819    1
1048704  sysadmin   informix  sysconstraints                en_US.819    1
1048723  sysadmin   informix  sysinherits                   en_US.819    1
1048731  sysadmin   informix  syserrors                     en_US.819    1
1048742  sysadmin   informix  sysxasourcetypes              en_US.819    1
1048750  sysadmin   informix  sysseclabelnames              en_US.819    1
1048794  sysadmin   informix  storagepool                   en_US.819    1
1048825  sysadmin   informix   124_51                       en_US.819    1
1048843  sysadmin   informix  mon_users                     en_US.819    1
1048856  sysadmin   informix  mon_prof_idx2                 en_US.819    1
1048861  unlogged   informix  syscolumns                    en_US.819    1
1048878  sysutils   informix  syssynonyms                   en_US.819    1
1048881  sysutils   informix  sysreferences                 en_US.819    1
1048888  sysutils   informix  sysprocauth                   en_US.819    1
1048891  sysutils   informix  systriggers                   en_US.819    1
1048912  sysutils   informix  sysaggregates                 en_US.819    1
1048942  sysutils   informix   102_10                       en_US.819    1
1048952  sysuser    informix  syscolumns                    en_US.819    1
1048991  logged     informix  systabauth                    en_US.819    1
1049051  unlogged   informix  sysprocauth                   en_US.819    1
1049055  mode_ansi  informix  sysprocauth                   en_US.819    1
1049110  mode_ansi  informix  sysinherits                   en_US.819    1
1049128  logged     informix  syslogmap                     en_US.819    1
1049222  utf8       informix  sysobjstate                   en_US.57372  1
1049259  utf8       informix  sysseclabelcomponentelements  en_US.57372  1
1049269  utf8       informix  syssecpolicycomponents        en_US.57372  1
1049297  unlogged   jleffler  dual                          en_US.819    1
1049298  stores     jleffler  dual                          en_US.819    1
1049303  stores     jleffler   100_1                        en_US.819    1
1049309  stores     jleffler  elements                      en_US.819    1
1049313  stores     jleffler   101_4                        en_US.819    1
1049318  stores     jleffler   101_5                        en_US.819    1
1049321  stores     jleffler   101_6                        en_US.819    1
1049341  stores     jleffler  compound                      en_US.819    1
1049343  stores     jleffler   102_18                       en_US.819    1
1049346  stores     jleffler   102_19                       en_US.819    1
1049351  stores     jleffler  compound_component            en_US.819    1
1049353  stores     jleffler   103_22                       en_US.819    1
1049356  stores     jleffler   103_23                       en_US.819    1
1049358  stores     jleffler   103_24                       en_US.819    1
1049367  stores     jleffler  isotopes                      en_US.819    1
1049368  stores     jleffler   104_30                       en_US.819    1
1049371  stores     jleffler   104_31                       en_US.819    1
1049382  stores     jleffler  named_isotopes                en_US.819    1
1049383  stores     jleffler   105_37                       en_US.819    1
1049385  stores     jleffler   105_38                       en_US.819    1

The 'table' such as " 105_38" are not tables but indexes. There may be other tables or views in sysmaster which satisfy your question better — but it is likely that querying sysmaster is the way to get the information you're after.

Related Question