Re[2]: List of All Indexes
Rajendra,
Try this sql.
Regards
David Ashby
select tabname,
idxname,
c1.colname,
c2.colname,
c3.colname,
c4.colname,
c5.colname,
c6.colname,
c7.colname,
c8.colname,
c9.colname,
c10.colname,
c11.colname,
c12.colname,
c13.colname,
c14.colname,
c15.colname,
c16.colname
from systables t,
syscolumns c1,
outer(syscolumns c2),
outer(syscolumns c3),
outer(syscolumns c4),
outer(syscolumns c5),
outer(syscolumns c6),
outer(syscolumns c7),
outer(syscolumns c8),
outer(syscolumns c9),
outer(syscolumns c10),
outer(syscolumns c11),
outer(syscolumns c12),
outer(syscolumns c13),
outer(syscolumns c14),
outer(syscolumns c15),
outer(syscolumns c16),
sysindexes i
where t.tabid > 99
and t.tabtype = 'T'
and t.tabid = i.tabid
and i.tabid = c1.tabid
and i.tabid = c2.tabid
and i.tabid = c3.tabid
and i.tabid = c4.tabid
and i.tabid = c5.tabid
and i.tabid = c6.tabid
and i.tabid = c7.tabid
and i.tabid = c8.tabid
and i.tabid = c9.tabid
and i.tabid = c10.tabid
and i.tabid = c11.tabid
and i.tabid = c12.tabid
and i.tabid = c13.tabid
and i.tabid = c14.tabid
and i.tabid = c15.tabid
and i.tabid = c16.tabid
and part1 = c1.colno
and part2 = c2.colno
and part3 = c3.colno
and part4 = c4.colno
and part5 = c5.colno
and part6 = c6.colno
and part7 = c7.colno
and part8 = c8.colno
and part9 = c9.colno
and part10 = c10.colno
and part11 = c11.colno
and part12 = c12.colno
and part13 = c13.colno
and part14 = c14.colno
and part15 = c15.colno
and part16 = c16.colno
order by tabname, idxname;
______________________________ Reply Separator _________________________________
Subject: Re: List of All Indexes
Author: <mickm@netcom.com (Mickey Mestel)> at WCA-INET
Date: 20/3/98 4:28 PM
Rajendra Singh (rajsingh@hotmail.com) wrote:
: I am trying to compose a single SQL query which will list all table names,
index
: names, and the columns on which those indexes were created. For example, say
I
: had created the index "customer" using the following query:
: "What is understood need not be discussed." - Loren Adams
i have a script that will create a geenric update satistics script for
all the tables in a database. the sql i use sounds like what you are looking
for , but i don't have access to it right now. e-mail me at
mickm@ix.netcom.com,
and i will send you the script and you can look at the output and the sql
statments.
mickm
--
_____________________________________________________________________________
Mickey Mestel mickm@netcom.com
-on a beach in thailand to a beautiful, stoned, norwegian woman:
"..yeah, it's just another foreign country without ice."
-----------------------------------------------------------------------------