how to extract all the tables in on the informix d
A user asked how to list the user (non-system) tables in an Informix database. Jonathan Leffler suggested dbschema/dbexport for schema or schema+data, and confirmed the user's own query — SELECT tabname FROM systables WHERE tabid > 99 AND tabtype = 'T' — is correct, since tabids 1–99 are catalog tables and tabtype 'T' excludes views, synonyms and sequences; for MODE ANSI databases, qualify as "informix".systables. John Miller noted DB-Access's "info tables" and "info columns for <table>" commands. After much confusion over the question, the user clarified he wanted table names plus owners one database at a time; malc_p pointed to the sysmaster database's systabnames table.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
Hi All, How to extract all the tables in informix database not system Thank you very much Regards Baskaran
On Wed, Nov 21, 2012 at 6:05 PM, medkba <medkba@gmail.com> wrote:
> How to extract all the tables in informix database not system
>
it depends on what you want, but either DB-Schema or DB-Export is likely to
be the tool you're looking for.
dbschema gives you the schema without the data.
dbexport gives you the schema and the data.
--
Jonathan Leffler <jonathan.leffler@gmail.com> #include <disclaimer.h>
Guardian of DBD::Informix - v2011.0612 - http://dbi.perl.org
"Blessed are we who can laugh at ourselves, for we shall never cease to be
amused."
--e89a8f22c4af74a43d04cf0d0cfd
Thank you jonathan, Is there any sql statement to extract table list
I have used this sql statement but not sure > 99 means that
SELECT tabname
FROM systables
WHERE tabid > 99
AND tabtype = "T"
Thank you
On Thu, Nov 22, 2012 at 11:27 AM, Jonathan Leffler
<jonathan.leffler@gmail.com> wrote:
> On Wed, Nov 21, 2012 at 6:05 PM, medkba <medkba@gmail.com> wrote:
>
>> How to extract all the tables in informix database not system
>>
>
> it depends on what you want, but either DB-Schema or DB-Export is likely to
> be the tool you're looking for.
>
> dbschema gives you the schema without the data.
> dbexport gives you the schema and the data.
>
> --
> Jonathan Leffler <jonathan.leffler@gmail.com> #include <disclaimer.h>
> Guardian of DBD::Informix - v2011.0612 - http://dbi.perl.org
> "Blessed are we who can laugh at ourselves, for we shall never cease to be
> amused."
>
> --e89a8f22c4af74a43d04cf0d0cfd
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
On Wed, Nov 21, 2012 at 7:35 PM, medkba <medkba@gmail.com> wrote:
> Thank you jonathan, Is there any sql statement to extract table list
>
> I have used this sql statement but not sure > 99 means that
>
> SELECT tabname
> FROM systables
> WHERE tabid > 99
> AND tabtype = "T">
Ah, somewhat different question from what I thought you were asking.
That statement works perfectly well. The tables in the system catalog have
number between 1 and 99; the first user-defined table has a tabid of 100.
The tabtype of "T" ensure you don't get synonyms, views, sequences (etc)
listed. Temporary tables are not listed because they're not recorded in
systables.
If you might need to deal with MODE ANSI databases, use
"informix".systables (or informix.systables) for the table name.
--
Jonathan Leffler <jonathan.leffler@gmail.com> #include <disclaimer.h>
Guardian of DBD::Informix - v2011.0612 - http://dbi.perl.org
"Blessed are we who can laugh at ourselves, for we shall never cease to be
amused."
--bcaec554d9d646e75004cf10909a
Sorry, actually, i would like to get table list from one of the
database tables not system tables
On Thu, Nov 22, 2012 at 3:39 PM, Jonathan Leffler
<jonathan.leffler@gmail.com> wrote:
> On Wed, Nov 21, 2012 at 7:35 PM, medkba <medkba@gmail.com> wrote:
>
>> Thank you jonathan, Is there any sql statement to extract table list
>>
>> I have used this sql statement but not sure > 99 means that
>>
>> SELECT tabname
>> FROM systables
>> WHERE tabid > 99
>> AND tabtype = "T">>
>
> Ah, somewhat different question from what I thought you were asking.
>
> That statement works perfectly well. The tables in the system catalog have
> number between 1 and 99; the first user-defined table has a tabid of 100.
> The tabtype of "T" ensure you don't get synonyms, views, sequences (etc)
> listed. Temporary tables are not listed because they're not recorded in
> systables.
>
> If you might need to deal with MODE ANSI databases, use
> "informix".systables (or informix.systables) for the table name.
>
> --
> Jonathan Leffler <jonathan.leffler@gmail.com> #include <disclaimer.h>
> Guardian of DBD::Informix - v2011.0612 - http://dbi.perl.org
> "Blessed are we who can laugh at ourselves, for we shall never cease to be
> amused."
>
> --bcaec554d9d646e75004cf10909a
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
Tabid less than 100 are system tables.
There is the built in command "info" which is like "load/unload" in
dbaccess.
Try "info tables"
There are many options to display stuff like indexes, columns ....
Try "info columns for table_name_here"
John F. Miller III
STSM, Embedability Architect
miller3@us.ibm.com
503-578-5645
IBM Informix Dynamic Server (IDS)
ids-bounces@iiug.org wrote on 11/21/2012 07:35:48 PM:
> From: "medkba" <medkba@gmail.com>
> To: ids@iiug.org,
> Date: 11/21/2012 07:36 PM
> Subject: Re: how to extract all the tables in on the in.... [28903]
> Sent by: ids-bounces@iiug.org
>
> Thank you jonathan, Is there any sql statement to extract table list
>
> I have used this sql statement but not sure > 99 means that
>
> SELECT tabname
> FROM systables
> WHERE tabid > 99
> AND tabtype = "T">
> Thank you
>
> On Thu, Nov 22, 2012 at 11:27 AM, Jonathan Leffler
> <jonathan.leffler@gmail.com> wrote:
> > On Wed, Nov 21, 2012 at 6:05 PM, medkba <medkba@gmail.com> wrote:
> >
> >> How to extract all the tables in informix database not system
> >>
> >
> > it depends on what you want, but either DB-Schema or DB-Export is
likely to
> > be the tool you're looking for.
> >
> > dbschema gives you the schema without the data.
> > dbexport gives you the schema and the data.
> >
> > --
> > Jonathan Leffler <jonathan.leffler@gmail.com> #include <disclaimer.h>
> > Guardian of DBD::Informix - v2011.0612 - http://dbi.perl.org
> > "Blessed are we who can laugh at ourselves, for we shall never cease to
be
> > amused."
> >
> > --e89a8f22c4af74a43d04cf0d0cfd
> >
> >
> >
>
*******************************************************************************
> > Forum Note: Use "Reply" to post a response in the discussion forum.
> >
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
On Wed, Nov 21, 2012 at 11:45 PM, medkba <medkba@gmail.com> wrote: > Sorry, actually, I would like to get table list from one of the > database tables not system tables > What do you mean? You can get the information from any table that you've created that contains the list of tables that you've created, but most people don't create such tables because (a) systables already keeps the list and (b) it is hard to maintain the table accurately up to date. Or, slightly less obtusely: The table that keeps a record of the tables in the database is systables. There is no other table from which to get the information, unless you go to sysmaster and go ferreting about in there (which is considerably more difficult to do). If you are looking for information schema tables, you have to install them (xpg4_is.sql in $INFORMIXDIR/etc, IIRC), but I'm not sure I'd recommend that. -- Jonathan Leffler <jonathan.leffler@gmail.com> #include <disclaimer.h> Guardian of DBD::Informix - v2011.0612 - http://dbi.perl.org "Blessed are we who can laugh at ourselves, for we shall never cease to be amused." --bcaec5555678ea536604cf186c0e
Hi, No problem, I want to get from all the tables what we created earlier and choose each database. Each database contains different tables right? Thank you On Fri, Nov 23, 2012 at 1:02 AM, Jonathan Leffler <jonathan.leffler@gmail.com> wrote: > On Wed, Nov 21, 2012 at 11:45 PM, medkba <medkba@gmail.com> wrote: > >> Sorry, actually, I would like to get table list from one of the >> database tables not system tables >> > > What do you mean? > > You can get the information from any table that you've created that > contains the list of tables that you've created, but most people don't > create such tables because (a) systables already keeps the list and (b) it > is hard to maintain the table accurately up to date. > > Or, slightly less obtusely: > > The table that keeps a record of the tables in the database is systables. > > There is no other table from which to get the information, unless you go to > sysmaster and go ferreting about in there (which is considerably more > difficult to do). > > If you are looking for information schema tables, you have to install them > (xpg4_is.sql in $INFORMIXDIR/etc, IIRC), but I'm not sure I'd recommend > that. > > -- > Jonathan Leffler <jonathan.leffler@gmail.com> #include <disclaimer.h> > Guardian of DBD::Informix - v2011.0612 - http://dbi.perl.org > "Blessed are we who can laugh at ourselves, for we shall never cease to be > amused." > > --bcaec5555678ea536604cf186c0e > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. >
On Thu, Nov 22, 2012 at 3:11 PM, medkba <medkba@gmail.com> wrote:
> No problem, I want to get from all the tables what we created earlier
> and choose each database. Each database contains different tables
> right?
>
I'm sorry; I'm having difficulty understanding you. Maybe I should go and
eat some turkey.
Your question "Each database contains different tables?" is understandable
(I think).
Yes, each database has an independent set of tables. If you have two
databases dbs1 and dbss2, there may be tables in dbs1 that have no matching
table in dbs2 and vice versa. There may be tables in dbs1 with the same
name but different structure from a table in dbs2 (and vice versa). And
there may be tables in dbs1 and dbs2 with the same name and structure;
however, it is not guaranteed that these tables have the same contents
(though that could also happen, usually not by accident). There are other
possible conditions (such as different names but same structure and
content), but they probably don't concern us here.
Your statement "I want to get from all the tables what we created earlier
and choose each database" is giving me comprehension problems. The words
are individually fine; it's just the assemblage that's causing me grief.
You previously said "I would like to get table list from one of the
database tables not system tables". And your original question was "How to
extract all the tables in Informix database not system". When I suggested
DB-Schema and DB-Export, you responded:
Is there any sql statement to extract table list
>
> I have used this sql statement but not sure > 99 means that
>
> SELECT tabname
> FROM systables
> WHERE tabid > 99
> AND tabtype = "T">
And I said that is a perfectly good way to get the list of table names in a
given database, but you came back with the question below, to which I gave
an expanded answer that started with 'What do you mean?', and you came back
yet again with the message at the top.
> On Fri, Nov 23, 2012 at 1:02 AM, Jonathan Leffler wrote:
> > On Wed, Nov 21, 2012 at 11:45 PM, medkba <medkba@gmail.com> wrote:
> >> Sorry, actually, I would like to get table list from one of the
> >> database tables not system tables
> >
> > What do you mean?
>
Please can you go back to the basics, and explain what it is that you want,
starting from scratch. Do you want a list of table names, or table names
plus owners,or table names plus owners plus the database that they're found
in, or something else? Are you wanting the list for one database at a
time, or for all databases? Do you need to know about columns or anything
else?
--
Jonathan Leffler <jonathan.leffler@gmail.com> #include <disclaimer.h>
Guardian of DBD::Informix - v2011.0612 - http://dbi.perl.org
"Blessed are we who can laugh at ourselves, for we shall never cease to be
amused."
--e89a8f2346659773d604cf2240ad
I want list of table names plus owner and other information one database at
a time
On Nov 23, 2012 1:41 PM, "Jonathan Leffler" <jonathan.leffler@gmail.com>
wrote:
> On Thu, Nov 22, 2012 at 3:11 PM, medkba <medkba@gmail.com> wrote:
>
> > No problem, I want to get from all the tables what we created earlier
> > and choose each database. Each database contains different tables
> > right?
> >
>
> I'm sorry; I'm having difficulty understanding you. Maybe I should go and
> eat some turkey.
>
> Your question "Each database contains different tables?" is understandable
> (I think).
>
> Yes, each database has an independent set of tables. If you have two
> databases dbs1 and dbss2, there may be tables in dbs1 that have no matching
> table in dbs2 and vice versa. There may be tables in dbs1 with the same
> name but different structure from a table in dbs2 (and vice versa). And
> there may be tables in dbs1 and dbs2 with the same name and structure;
> however, it is not guaranteed that these tables have the same contents
> (though that could also happen, usually not by accident). There are other
> possible conditions (such as different names but same structure and
> content), but they probably don't concern us here.
>
> Your statement "I want to get from all the tables what we created earlier
> and choose each database" is giving me comprehension problems. The words
> are individually fine; it's just the assemblage that's causing me grief.
> You previously said "I would like to get table list from one of the
> database tables not system tables". And your original question was "How to
> extract all the tables in Informix database not system". When I suggested
> DB-Schema and DB-Export, you responded:
>
> Is there any sql statement to extract table list
> >
> > I have used this sql statement but not sure > 99 means that
> >
> > SELECT tabname
> > FROM systables
> > WHERE tabid > 99
> > AND tabtype = "T"> >
>
> And I said that is a perfectly good way to get the list of table names in a
> given database, but you came back with the question below, to which I gave
> an expanded answer that started with 'What do you mean?', and you came back
> yet again with the message at the top.
>
> > On Fri, Nov 23, 2012 at 1:02 AM, Jonathan Leffler wrote:
> > > On Wed, Nov 21, 2012 at 11:45 PM, medkba <medkba@gmail.com> wrote:
> > >> Sorry, actually, I would like to get table list from one of the
> > >> database tables not system tables
> > >
> > > What do you mean?
> >
>
> Please can you go back to the basics, and explain what it is that you want,
> starting from scratch. Do you want a list of table names, or table names
> plus owners,or table names plus owners plus the database that they're found
> in, or something else? Are you wanting the list for one database at a
> time, or for all databases? Do you need to know about columns or anything
> else?
>
> --
> Jonathan Leffler <jonathan.leffler@gmail.com> #include <disclaimer.h>
> Guardian of DBD::Informix - v2011.0612 - http://dbi.perl.org
> "Blessed are we who can laugh at ourselves, for we shall never cease to be
> amused."
>
> --e89a8f2346659773d604cf2240ad
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--bcaec50406eaeb4ad804cf233bf3
On 23/11/2012 05:55, medkba wrote: > I want list of table names plus owner and other information one database at > a time > In that case, the sysmaster database is your friend. In particular, Read The Fine Manual and look for information about what the table "systabnames" can offer you.