index count

Is there a way to find out the total number of indexes
(clustered and non-clustered) on a given database, either
through the use of QA or EM? I'm currently running
sp_helpindex against all tables in my database and then
manually counting each index.
Thanks.
SELECT COUNT(*) FROM sysindexes
WHERE indid BETWEEN 1 AND 254
Indexes with indid 0 are heaps (tables without a clustered index), indid 1
are clustered indexes, indid 2-254 are non clustered indexes and indid 255
are used for text/ntext/images columns
Jacco Schalkwijk
SQL Server MVP
"Rob" <anonymous@.discussions.microsoft.com> wrote in message
news:07f501c4ad40$fe7d1920$a501280a@.phx.gbl...
> Is there a way to find out the total number of indexes
> (clustered and non-clustered) on a given database, either
> through the use of QA or EM? I'm currently running
> sp_helpindex against all tables in my database and then
> manually counting each index.
> Thanks.
|||Also not that this will include statistics and hypothetical indexes. These can be filtered out using
INDEXPEROPERTY.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Jacco Schalkwijk" <jacco.please.reply@.to.newsgroups.mvps.org.invalid > wrote in message
news:%23heAEIUrEHA.1272@.TK2MSFTNGP09.phx.gbl...
> SELECT COUNT(*) FROM sysindexes
> WHERE indid BETWEEN 1 AND 254
> Indexes with indid 0 are heaps (tables without a clustered index), indid 1 are clustered indexes,
> indid 2-254 are non clustered indexes and indid 255 are used for text/ntext/images columns
> --
> Jacco Schalkwijk
> SQL Server MVP
>
> "Rob" <anonymous@.discussions.microsoft.com> wrote in message
> news:07f501c4ad40$fe7d1920$a501280a@.phx.gbl...
>
|||select count(*) from (SELECT sysobjects.name, sysobjects.id,
sysindexkeys.indid
FROM sysindexes INNER JOIN
sysobjects ON sysindexes.id = sysobjects.id INNER JOIN
sysindexkeys ON sysindexes.id = sysindexkeys.id
GROUP BY sysobjects.name, sysobjects.id, sysindexkeys.indid) drv
ll give the current databases index count
"Rob" <anonymous@.discussions.microsoft.com> wrote in message
news:07f501c4ad40$fe7d1920$a501280a@.phx.gbl...
> Is there a way to find out the total number of indexes
> (clustered and non-clustered) on a given database, either
> through the use of QA or EM? I'm currently running
> sp_helpindex against all tables in my database and then
> manually counting each index.
> Thanks.
|||Oops, forgot about those.
SELECT COUNT(*) FROM sysindexes
WHERE indid BETWEEN 1 AND 254
AND INDEXPROPERTY (ID, name, 'IsHypothetical') = 0
AND INDEXPROPERTY (ID, name, 'IsStatistics') = 0
Jacco Schalkwijk
SQL Server MVP
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:OcFKtLUrEHA.3748@.TK2MSFTNGP09.phx.gbl...
> Also not that this will include statistics and hypothetical indexes. These
> can be filtered out using INDEXPEROPERTY.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Jacco Schalkwijk" <jacco.please.reply@.to.newsgroups.mvps.org.invalid >
> wrote in message news:%23heAEIUrEHA.1272@.TK2MSFTNGP09.phx.gbl...
>
|||Thanks guys, that was really helpful. Any ideas on how to
apply the same filter in SQL6.5, since the INDEXPROPERTY
function isn't available in 6.5?
Thanks again.

>--Original Message--
>Oops, forgot about those.
>SELECT COUNT(*) FROM sysindexes
>WHERE indid BETWEEN 1 AND 254
>AND INDEXPROPERTY (ID, name, 'IsHypothetical') = 0
>AND INDEXPROPERTY (ID, name, 'IsStatistics') = 0
>--
>Jacco Schalkwijk
>SQL Server MVP
>
>"Tibor Karaszi"
<tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
in[vbcol=seagreen]
>message news:OcFKtLUrEHA.3748@.TK2MSFTNGP09.phx.gbl...
hypothetical indexes. These[vbcol=seagreen]
<jacco.please.reply@.to.newsgroups.mvps.org.invalid >[vbcol=seagreen]
23heAEIUrEHA.1272@.TK2MSFTNGP09.phx.gbl...[vbcol=seagreen]
clustered index), indid[vbcol=seagreen]
indexes and indid[vbcol=seagreen]
message[vbcol=seagreen]
either[vbcol=seagreen]
then
>
>.
>
|||I forget how a text page is represented in 6.5 sysindexes. But... you won't
have hypothetical indexes and I don't think stats show up as a row in
sysindexes. It's been sooo long, I just don't remember.
But I think you'll be safe simply looking at user tables with an indid
between 1-255 (or 254 if a row is there for text/image)
Brian Moran
Principal Mentor
Solid Quality Learning
SQL Server MVP
http://www.solidqualitylearning.com
"Rob" <anonymous@.discussions.microsoft.com> wrote in message
news:171201c4ad4c$1569c6f0$a301280a@.phx.gbl...[vbcol=seagreen]
> Thanks guys, that was really helpful. Any ideas on how to
> apply the same filter in SQL6.5, since the INDEXPROPERTY
> function isn't available in 6.5?
> Thanks again.
> <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
> in
> hypothetical indexes. These
> <jacco.please.reply@.to.newsgroups.mvps.org.invalid >
> 23heAEIUrEHA.1272@.TK2MSFTNGP09.phx.gbl...
> clustered index), indid
> indexes and indid
> message
> either
> then
|||SQL 6.5 didn't have column stats (only those associated with indexes) and it
didn't have hypothetical indexes, so you don't need to check for either of
these conditions.
HTH
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Rob" <anonymous@.discussions.microsoft.com> wrote in message
news:171201c4ad4c$1569c6f0$a301280a@.phx.gbl...[vbcol=seagreen]
> Thanks guys, that was really helpful. Any ideas on how to
> apply the same filter in SQL6.5, since the INDEXPROPERTY
> function isn't available in 6.5?
> Thanks again.
> <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
> in
> hypothetical indexes. These
> <jacco.please.reply@.to.newsgroups.mvps.org.invalid >
> 23heAEIUrEHA.1272@.TK2MSFTNGP09.phx.gbl...
> clustered index), indid
> indexes and indid
> message
> either
> then