What would be the query to check that a table exists in Informix ?
How to check if a table exists in Informix
informixmetadatasystem-tablestable
Related Solutions
You say the directory has rw
permission; does it also have x
permission. You need x
permission to access the files in a directory (so you might set the permissions on the directory to ... gritted teeth ... 777, but you shouldn't. Is the directory owned by user informix
and does it belong to group informix
? Ideally, it should be owned by informix:informix
, and the permissions should be 770 (or drwxrwx---
).
I think understand your problem.
The dbaccess break lines at the middle of the statement and the table name goes to line bellow... Something like this (dbschema output)
create unique index "informix".ix1c_h_dbases_2012_06 on "informix"
.h_dbases_2012_06 (seq,site_name) using btree filtering
I consider two solutions 100% functional...
First suggestion
Parse the dbschema with awk to join the lines until found a semicolon.
This will give to you long lines...
Then you can work little more with awk/sed to get only the index and table name... not so hard.
But this probably will become a shell script.
$ dbschema -d dba -t h_dbases_2012_06| awk '{printf $0} /;/{print ""}' | egrep "create.index"
create index "informix".ix10_h_dbases_2012_06 on "informix".h_dbases_2012_06 (datini,pagwrites) using btree ;
create index "informix".ix11_h_dbases_2012_06 on "informix".h_dbases_2012_06 (datini,total_sorts) using btree ;
create index "informix".ix12_h_dbases_2012_06 on "informix".h_dbases_2012_06 (datini,isreads) using btree ;
create index "informix".ix13_h_dbases_2012_06 on "informix".h_dbases_2012_06 (datini,iswrites) using btree ;
create index "informix".ix14_h_dbases_2012_06 on "informix".h_dbases_2012_06 (datini,iscommits) using btree ;
create index "informix".ix15_h_dbases_2012_06 on "informix".h_dbases_2012_06 (datini,net_write_mb) using btree ;
create index "informix".ix16_h_dbases_2012_06 on "informix".h_dbases_2012_06 (datini,horini) using btree ;
create index "informix".ix1_h_dbases_2012_06 on "informix".h_dbases_2012_06 (seq) using btree ;
create index "informix".ix1b_h_dbases_2012_06 on "informix".h_dbases_2012_06 (seq,hostname) using btree ;
create index "informix".ix2_h_dbases_2012_06 on "informix".h_dbases_2012_06 (datini,username) using btree ;
create index "informix".ix3_h_dbases_2012_06 on "informix".h_dbases_2012_06 (datini,cpu_time) using btree ;
create index "informix".ix4_h_dbases_2012_06 on "informix".h_dbases_2012_06 (datini,programa) using btree ;
create index "informix".ix5_h_dbases_2012_06 on "informix".h_dbases_2012_06 (datini,tarefa) using btree ;
create index "informix".ix6_h_dbases_2012_06 on "informix".h_dbases_2012_06 (datini,hostname) using btree ;
create index "informix".ix7_h_dbases_2012_06 on "informix".h_dbases_2012_06 (datini,maxlog_kb) using btree ;
create index "informix".ix8_h_dbases_2012_06 on "informix".h_dbases_2012_06 (datini,net_read_mb) using btree ;
create index "informix".ix9_h_dbases_2012_06 on "informix".h_dbases_2012_06 (datini,pagereads) using btree ;
Second suggestion
Query the database.
For this , they should be a script and your user should have access to database with dbaccess
$ cat x.sh
dbaccess -e - <<EOF
database $1 ;
select t.tabname[1,20] as table, i.idxname[1,20] as idx
from systables t , sysindices i
where t.tabid = i.tabid
and t.tabname matches lower("$2*")
and t.tabid > 99
order by 1,2
;
EOF
$ sh x.sh dba h_dbases_2014_04
database dba ;
Database selected.
select t.tabname[1,20] as table, i.idxname[1,20] as idx
from systables t , sysindices i
where t.tabid = i.tabid
and t.tabname matches lower("h_dbases_2014_04*")
and t.tabid > 99
order by 1,2
;
table idx
h_dbases_2014_04 ix10_h_dbases_2014_0
h_dbases_2014_04 ix11_h_dbases_2014_0
h_dbases_2014_04 ix12_h_dbases_2014_0
h_dbases_2014_04 ix13_h_dbases_2014_0
h_dbases_2014_04 ix14_h_dbases_2014_0
h_dbases_2014_04 ix15_h_dbases_2014_0
h_dbases_2014_04 ix16_h_dbases_2014_0
h_dbases_2014_04 ix1_h_dbases_2014_04
h_dbases_2014_04 ix1b_h_dbases_2014_0
h_dbases_2014_04 ix1c_h_dbases_2014_0
h_dbases_2014_04 ix2_h_dbases_2014_04
h_dbases_2014_04 ix3_h_dbases_2014_04
h_dbases_2014_04 ix4_h_dbases_2014_04
h_dbases_2014_04 ix5_h_dbases_2014_04
h_dbases_2014_04 ix6_h_dbases_2014_04
h_dbases_2014_04 ix7_h_dbases_2014_04
h_dbases_2014_04 ix8_h_dbases_2014_04
h_dbases_2014_04 ix9_h_dbases_2014_04
18 row(s) retrieved.
Database closed.
Best Answer
To check whether an Informix table exists, use the following query:
If the result is not 0, then the table exists.