Index Report
Tony wanted a simple report listing every table's indexes (and ultimately the indexed columns) for a whole database. Suggestions included joining systables with sysindexes, a shell loop running "info indexes for <table>" through dbaccess, piping dbschema -ss through an awk script (index/table names only), a join of systables/sysindexes/syscolumns matching part1..part10 against colno to get column names, reusing the 4GL code from dbdiff2, and Art Kagel's myschema utility (utils2_ak), which he noted handles functional and non-standard index types the sysindexes view misses on 9.x. No single answer was confirmed as adopted.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
Hi, Does someone have any easy way to generate a basic report listing just the indexes of all tables within a database? Example: Table: customer Indexes: 1-customer_id 2-customer_city Thank you. Tony
You might take a look at http://www.aquafold.com Aqua data studio will do something similar to what you want. Chris S. Demeis, Tony wrote: > Hi, > Does someone have any easy way to generate a basic report listing just the > indexes of all tables within a database? > > Example: > > Table: customer > Indexes: > 1-customer_id > 2-customer_city > > Thank you. > Tony > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > >
Something like
Select tabname, idxname from systables a, sysindexes b
Where a.tabid = b.tabid and a.tabid >99 and a.tabtype = "T"
Not the same format u want but u can manupliate the sql
I'm a great believer in luck, and I find the harder I work, the more I
have of it. - Thomas Jefferson (1743-1826) 3rd President of the United
States
-----Original Message-----
From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of
Demeis, Tony
Sent: Friday, May 19, 2006 8:59 AM
To: ids@iiug.org
Subject: Index Report [6766]
Hi,
Does someone have any easy way to generate a basic report listing just
the indexes of all tables within a database?
Example:
Table: customer
Indexes:
1-customer_id
2-customer_city
Thank you.
Tony
************************************************************************
*******
Forum Note: Use "Reply" to post a response in the discussion forum.
check the sysindexes table. j. -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org]On Behalf Of Demeis, Tony Sent: Friday, May 19, 2006 11:59 AM To: ids@iiug.org Subject: Index Report [6766] Hi, Does someone have any easy way to generate a basic report listing just the indexes of all tables within a database? Example: Table: customer Indexes: 1-customer_id 2-customer_city Thank you. Tony **************************************************************************** *** Forum Note: Use "Reply" to post a response in the discussion forum.
Will this do?:
select t.tabname, i.idxname
from systables t, sysindexes i
order by 1,2;
Art S. Kagel
----- Original Message -----
From: Tony Demeis <ids@iiug.org>
At: 5/19 12:02:16
Hi,
Does someone have any easy way to generate a basic report listing just the
indexes of all tables within a database?
Example:
Table: customer
Indexes:
1-customer_id
2-customer_city
Thank you.
Tony
*******************************************************************************
Forum Note: Use "Reply" to post a response in the discussion forum.
Demeis, Tony wrote:
> Hi,
> Does someone have any easy way to generate a basic report listing just the
> indexes of all tables within a database?
>
> Example:
>
> Table: customer
> Indexes:
> 1-customer_id
> 2-customer_city
>
> Thank you.
> Tony
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
>
Hi,
you can try something like this as first try:
#!/bin/sh
dbaccess YOURDB <<EOF
unload to tab.lst delimiter ""
select tabname from informix.systables wheretabid > 99
EOF
for TAB in `cat tab.lst`
do
echo "info indexes for $TAB;">>get_idx.sql
done
dbaccess -e YOURDB get_idx.sql >idx.out 2>&1
hth
:-) Jochen
Or try this:
dbschema -d mydatabase -ss | gawk '
BEGIN{in_idx=0; last=""; table="~~~~~~~~~~~~~~"}
/create (index|unique|cluster)/ {
in_idx=1;
if ($2=="index") {table=$5; indexnm=$3;}
else if ($3=="index") {table=$6; indexnm=$4;}
else if ($4=="index") {table=$7; indexnm=$5;}
}
/create (index|unique|cluster)/ && in_idx==1 && table != last {
printf "%s:\\
", table;
last=table;
}
/create (index|unique|cluster)/ && in_idx==1 {
printf "\\\\t%s\\
", indexnm;
in_idx=0;
next;
}
/;/{in_idx=0; last=table;}
{next;}
'
Art S. Kagel
----- Original Message -----
From: Tony Demeis <ids@iiug.org>
At: 5/19 12:02:16
Hi,
Does someone have any easy way to generate a basic report listing just the
indexes of all tables within a database?
Example:
Table: customer
Indexes:
1-customer_id
2-customer_city
Thank you.
Tony
*******************************************************************************
Forum Note: Use "Reply" to post a response in the discussion forum.
Hi Art,
I'm not looking for the index names but instead a list of the indexed
columns for each table.
Does the following just give the name?
Thanks,
Tony
-----Original Message-----
From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of ART
KAGEL, ....
Sent: Friday, May 19, 2006 1:57 PM
To: ids@iiug.org
Subject: Re: Index Report [6780]
Or try this:
dbschema -d mydatabase -ss | gawk '
BEGIN{in_idx=0; last=""; table="~~~~~~~~~~~~~~"} /create
(index|unique|cluster)/ {
in_idx=1;
if ($2=="index") {table=$5; indexnm=$3;}
else if ($3=="index") {table=$6; indexnm=$4;}
else if ($4=="index") {table=$7; indexnm=$5;} } /create
(index|unique|cluster)/ && in_idx==1 && table != last {
printf "%s:\\
", table;
last=table;
}
/create (index|unique|cluster)/ && in_idx==1 {
printf "\\\\t%s\\
", indexnm;
in_idx=0;
next;
}
/;/{in_idx=0; last=table;}
{next;}
'
Art S. Kagel
----- Original Message -----
From: Tony Demeis <ids@iiug.org>
At: 5/19 12:02:16
Hi,
Does someone have any easy way to generate a basic report listing just the
indexes of all tables within a database?
Example:
Table: customer
Indexes:
1-customer_id
2-customer_city
Thank you.
Tony
****************************************************************************
***
Forum Note: Use "Reply" to post a response in the discussion forum.
****************************************************************************
***
Forum Note: Use "Reply" to post a response in the discussion forum.
You can also steal the 4gl code from dbdiff2 out on iiug.org - it builds a
list of indices and all column names.
j.
-----Original Message-----
From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org]On Behalf Of
Demeis, Tony
Sent: Friday, May 19, 2006 3:03 PM
To: ids@iiug.org
Subject: RE: Index Report [6784]
Hi Art,
I'm not looking for the index names but instead a list of the indexed
columns for each table.
Does the following just give the name?
Thanks,
Tony
-----Original Message-----
From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of ART
KAGEL, ....
Sent: Friday, May 19, 2006 1:57 PM
To: ids@iiug.org
Subject: Re: Index Report [6780]
Or try this:
dbschema -d mydatabase -ss | gawk '
BEGIN{in_idx=0; last=""; table="~~~~~~~~~~~~~~"} /create
(index|unique|cluster)/ {
in_idx=1;
if ($2=="index") {table=$5; indexnm=$3;}
else if ($3=="index") {table=$6; indexnm=$4;}
else if ($4=="index") {table=$7; indexnm=$5;} } /create
(index|unique|cluster)/ && in_idx==1 && table != last {
printf "%s:\\
", table;
last=table;
}
/create (index|unique|cluster)/ && in_idx==1 {
printf "\\\\t%s\\
", indexnm;
in_idx=0;
next;
}
/;/{in_idx=0; last=table;}
{next;}
'
Art S. Kagel
----- Original Message -----
From: Tony Demeis <ids@iiug.org>
At: 5/19 12:02:16
Hi,
Does someone have any easy way to generate a basic report listing just the
indexes of all tables within a database?
Example:
Table: customer
Indexes:
1-customer_id
2-customer_city
Thank you.
Tony
****************************************************************************
***
Forum Note: Use "Reply" to post a response in the discussion forum.
****************************************************************************
***
Forum Note: Use "Reply" to post a response in the discussion forum.
****************************************************************************
***
Forum Note: Use "Reply" to post a response in the discussion forum.
Will try.
Thanks.
-----Original Message-----
From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of Jack
Parker
Sent: Friday, May 19, 2006 3:19 PM
To: ids@iiug.org
Subject: RE: Index Report [6785]
You can also steal the 4gl code from dbdiff2 out on iiug.org - it builds a
list of indices and all column names.
j.
-----Original Message-----
From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org]On Behalf Of Demeis,
Tony
Sent: Friday, May 19, 2006 3:03 PM
To: ids@iiug.org
Subject: RE: Index Report [6784]
Hi Art,
I'm not looking for the index names but instead a list of the indexed
columns for each table.
Does the following just give the name?
Thanks,
Tony
-----Original Message-----
From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of ART
KAGEL, ....
Sent: Friday, May 19, 2006 1:57 PM
To: ids@iiug.org
Subject: Re: Index Report [6780]
Or try this:
dbschema -d mydatabase -ss | gawk '
BEGIN{in_idx=0; last=""; table="~~~~~~~~~~~~~~"} /create
(index|unique|cluster)/ {
in_idx=1;
if ($2=="index") {table=$5; indexnm=$3;}
else if ($3=="index") {table=$6; indexnm=$4;}
else if ($4=="index") {table=$7; indexnm=$5;} } /create
(index|unique|cluster)/ && in_idx==1 && table != last {
printf "%s:\\
", table;
last=table;
}
/create (index|unique|cluster)/ && in_idx==1 {
printf "\\\\t%s\\
", indexnm;
in_idx=0;
next;
}
/;/{in_idx=0; last=table;}
{next;}
'
Art S. Kagel
----- Original Message -----
From: Tony Demeis <ids@iiug.org>
At: 5/19 12:02:16
Hi,
Does someone have any easy way to generate a basic report listing just the
indexes of all tables within a database?
Example:
Table: customer
Indexes:
1-customer_id
2-customer_city
Thank you.
Tony
****************************************************************************
***
Forum Note: Use "Reply" to post a response in the discussion forum.
****************************************************************************
***
Forum Note: Use "Reply" to post a response in the discussion forum.
****************************************************************************
***
Forum Note: Use "Reply" to post a response in the discussion forum.
****************************************************************************
***
Forum Note: Use "Reply" to post a response in the discussion forum.
Hi:
Maybe this query is your looking for:
select tabname, idxname, colname
from systables a, sysindexes b, syscolumns c
where a.tabid> 99 and a.tabid=b.tabid and
b.tabid=c.tabid and
(
b.part1=colno or
b.part2=colno or
b.part3=colno or
b.part4=colno or
b.part5=colno or
b.part6=colno or
b.part7=colno or
b.part8=colno or
b.part9=colno or
b.part10=colno
)
-----Mensaje original-----
De: Demeis, Tony [mailto:Tony.Demeis@moh.gov.on.ca]
Enviado el: Viernes, 19 de Mayo de 2006 02:03 p.m.
Para: ids@iiug.org
Asunto: RE: Index Report [6784]
Hi Art,
I'm not looking for the index names but instead a list of the indexed
columns for each table.
Does the following just give the name?
Thanks,
Tony
-----Original Message-----
From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of ART
KAGEL, ....
Sent: Friday, May 19, 2006 1:57 PM
To: ids@iiug.org
Subject: Re: Index Report [6780]
Or try this:
dbschema -d mydatabase -ss | gawk '
BEGIN{in_idx=0; last=""; table="~~~~~~~~~~~~~~"} /create
(index|unique|cluster)/ {
in_idx=1;
if ($2=="index") {table=$5; indexnm=$3;}
else if ($3=="index") {table=$6; indexnm=$4;}
else if ($4=="index") {table=$7; indexnm=$5;} } /create
(index|unique|cluster)/ && in_idx==1 && table != last {
printf "%s:\\
", table;
last=table;
}
/create (index|unique|cluster)/ && in_idx==1 {
printf "\\\\t%s\\
", indexnm;
in_idx=0;
next;
}
/;/{in_idx=0; last=table;}
{next;}
'
Art S. Kagel
----- Original Message -----
From: Tony Demeis <ids@iiug.org>
At: 5/19 12:02:16
Hi,
Does someone have any easy way to generate a basic report listing just the
indexes of all tables within a database?
Example:
Table: customer
Indexes:
1-customer_id
2-customer_city
Thank you.
Tony
****************************************************************************
***
Forum Note: Use "Reply" to post a response in the discussion forum.
****************************************************************************
***
Forum Note: Use "Reply" to post a response in the discussion forum.
****************************************************************************
***
Forum Note: Use "Reply" to post a response in the discussion forum.
Perhaps below can help for you:
CREATE FUNCTION dbo.fnIsColumnPrimaryKey(@sTableName varchar(128),
@nColumnName varchar(128))RETURNS bit
AS
BEGIN
DECLARE @nTableID int,
@nIndexID int,
@i int
SET @nTableID = OBJECT_ID(@sTableName)
SELECT @nIndexID = indid
FROM sysindexes
WHERE id = @nTableID
AND indid BETWEEN 1 And 254
AND (status & 2048) = 2048
IF @nIndexID Is Null
RETURN 0
IF @nColumnName IN
(SELECT sc.[name]
FROM sysindexkeys sik
INNER JOIN syscolumns sc ON sik.id = sc.id AND sik.colid = sc.colid
WHERE sik.id = @nTableID
AND sik.indid = @nIndexID)
BEGIN
RETURN 1
END
RETURN 0
END
----- Original Message -----
From: "Demeis, Tony" <Tony.Demeis@moh.gov.on.ca>
To: <ids@iiug.org>
Sent: Friday, May 19, 2006 11:59 PM
Subject: Index Report [6766]
>
>
> Hi,
> Does someone have any easy way to generate a basic report listing just the
> indexes of all tables within a database?
>
> Example:
>
> Table: customer
> Indexes:
> 1-customer_id
> 2-customer_city
>
> Thank you.
> Tony
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
>
Yes, the awk script only prints the index names and tablenames. Sorry it
looked like that's what you were looking for. You COULD just get my dbschema
replacement utility, myschema in the package utils2_ak. Myschema will
optionally print CREATE TABLE statements to one file and index definitions,
constraints, etc to a second file. Or, you could just extract the index
analysis code from myschema for your own uses (non-commercial of course ;-).
The code is in the file print_indexes and analyses all index types including
functional indexes and indexes using non-std access methods neither of which
you
can do by reading the sysindexes VIEW in 9.xx+, that only works completely in
7.xx.
Art S. Kagel
----- Original Message -----
From: Tony Demeis <ids@iiug.org>
At: 5/19 15:06:14
Hi Art,
I'm not looking for the index names but instead a list of the indexed
columns for each table.
Does the following just give the name?
Thanks,
Tony
-----Original Message-----
From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of ART
KAGEL, ....
Sent: Friday, May 19, 2006 1:57 PM
To: ids@iiug.org
Subject: Re: Index Report [6780]
Or try this:
dbschema -d mydatabase -ss | gawk '
BEGIN{in_idx=0; last=""; table="~~~~~~~~~~~~~~"} /create
(index|unique|cluster)/ {
in_idx=1;
if ($2=="index") {table=$5; indexnm=$3;}
else if ($3=="index") {table=$6; indexnm=$4;}
else if ($4=="index") {table=$7; indexnm=$5;} } /create
(index|unique|cluster)/ && in_idx==1 && table != last {
printf "%s:\\
", table;
last=table;
}
/create (index|unique|cluster)/ && in_idx==1 {
printf "\\\\t%s\\
", indexnm;
in_idx=0;
next;
}
/;/{in_idx=0; last=table;}
{next;}
'
Art S. Kagel
----- Original Message -----
From: Tony Demeis <ids@iiug.org>
At: 5/19 12:02:16
Hi,
Does someone have any easy way to generate a basic report listing just the
indexes of all tables within a database?
Example:
Table: customer
Indexes:
1-customer_id
2-customer_city
Thank you.
Tony
****************************************************************************
***
Forum Note: Use "Reply" to post a response in the discussion forum.
****************************************************************************
***
Forum Note: Use "Reply" to post a response in the discussion forum.
*******************************************************************************
Forum Note: Use "Reply" to post a response in the discussion forum.