0

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 ;
Improve this question
2
  • What do you mean by 'schema'? Informix servers store a number of separate databases, each of which is a self-contained set of tables with its own system catalog. Within a database, to the extent that there is a 'schema', it is the 'owner' for a group of tables. The CREATE SCHEMA statement can be used to create a number of tables and indexes in a single statement with the same owner. All DDL statements apply to a single database.
    – Jonathan Leffler
    Commented Apr 15, 2019 at 0:14
  • So, in context, are you seeking to find which database holds a table with a given name within a particular server? So you'd like SELECT owner, tabname FROM …somewhere… WHERE tabname MATCHES "*something*"? The query you shows lists all table and view names other than the system catalog.
    – Jonathan Leffler
    Commented Apr 15, 2019 at 0:15

1 Answer 1

Reset to default
0

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.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.