| View previous topic :: View next topic |
| Author |
Message |
zefrim
Beginner
Joined: 29 Jun 2006
Posts: 22
Topics: 19
|
 Posted: Fri Dec 10, 2010 9:18 am Post subject: List tables with Index count using SQL |
 |
|
HI there
maybe someone can help me:
I would like to know how many indexes a table.
The output should look like this:
| Code: |
Creator Name TOTAL-IX
-------------------------------
crea1 tab1 5
crea2 tab2 0
.
.
. |
It should also list the tables, which will have no index
Maybe someone knows this
Thanks Zefrim |
|
| Back to top |
|
 |
kolusu
Site Admin
Joined: 26 Nov 2002
Posts: 12375
Topics: 75
Location: San Jose
|
| Posted: Fri Dec 10, 2010 12:42 pm Post subject: |
|
|
zefrim,
Unless I am missing something, isn't it a simple query on SYSIBM.SYSINDEXES catalog table?
| Code: |
SELECT CHAR(CREATOR,18) AS CREATOR
,CHAR(TBNAME,18) AS TBNAME
,COUNT(*) AS TOTAL_IX
FROM SYSIBM.SYSINDEXES
GROUP BY CREATOR,TBNAME
; |
Read more about SYSIBM.SYSINDEXES catalog table here
http://publibz.boulder.ibm.com/cgi-bin/bookmgr_OS390/BOOKS/dsnsqj12/F.30
Kolusu |
|
| Back to top |
|
|
zefrim
Beginner
Joined: 29 Jun 2006
Posts: 22
Topics: 19
|
| Posted: Mon Dec 13, 2010 3:57 am Post subject: |
|
|
HI
thanks for the query but I also need those tables which
have no index, so it is somewhat more complicated
Zefrim |
|
| Back to top |
|
|
kolusu
Site Admin
Joined: 26 Nov 2002
Posts: 12375
Topics: 75
Location: San Jose
|
| Posted: Mon Dec 13, 2010 9:55 am Post subject: |
|
|
| zefrim wrote: | HI
thanks for the query but I also need those tables which
have no index, so it is somewhat more complicated
Zefrim |
huh? There will be hundreds of tables listed in the sysibm.systables How are you going to narrow without knowing the creator or anything to start with? Do you need to filter based on the creator?
Kolusu |
|
| Back to top |
|
|
papadi
Supermod
Joined: 20 Oct 2009
Posts: 594
Topics: 1
|
| Posted: Mon Dec 13, 2010 2:26 pm Post subject: |
|
|
Read the SYSTABLES and join this with the SYSINDEXES . . .
_________________
All the best,
di |
|
| Back to top |
|
|
|
|
|
FAQ
Search
Quick Manuals
Register
Profile
Log in to check your private messages
Log in
