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...
>> 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 (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...
>> 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.
>>
>|||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
>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...
>> 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.
>>
>>
>
>.
>|||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...
> 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
> >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...
> >> 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.
> >>
> >>
> >>
> >>
> >
> >
> >.
> >|||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...
> 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
>>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...
>> 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.
>>
>>
>>
>>.