Index count

How can I find the number of indexes created on a user database (Only
clustered and Non-clustered indexes in user tables - not system).
Thanks."DXC" <DXC@.discussions.microsoft.com> wrote in message
news:5F002D25-DB29-49A3-9F35-36E782D1882B@.microsoft.com...
> How can I find the number of indexes created on a user database (Only
> clustered and Non-clustered indexes in user tables - not system).
>
Select count(*) from sysindexes
where id in (select id from sysobjects where type = 'U')
and indid <> 255
255 is Text column.|||Set objSqlServer to your sql connection
Sub ListIndexes(strDBName)
WSCript.Echo "Database:" & Trim(strDBName)
Set oDatabase = objSqlServer.Databases(Trim(strDBName))
For Each Table In oDatabase.Tables
If NOT Table.SystemObject Then
WSCript.Echo Table.Name & " (" & Table.Indexes.Count & " indexes)"
<--here is the actual count property
For Each Index in Table.Indexes
If NOT Index.StatisticsIndex Then
WSCript.Echo vbTab & Index.Name & " (Stat: " & Index.StatisticsIndex &
")"
For Each Column in Index.ListIndexedColumns( )
WSCript.Echo vbTab & vbTab & "[" & Column.Name & "]"
Next
WSCript.Echo vbSpace
End If
Next
End IF
Next
End Sub
Or... more to the point
Set objSqlServer = YourSQLServerConnection
Set oDatabase = objSqlServer.Databases(TheDatabase)
For Each Table In oDatabase.Tables
If NOT Table.SystemObject Then
WSCript.StdOout.WriteLine Table.Indexes.Count
End If
Next
"DXC" <DXC@.discussions.microsoft.com> wrote in message
news:5F002D25-DB29-49A3-9F35-36E782D1882B@.microsoft.com...
> How can I find the number of indexes created on a user database (Only
> clustered and Non-clustered indexes in user tables - not system).
> Thanks.|||Not quite, in SQL 2000 statistics also take up an indid value in
sysindexes, so you would need to eliminate them also.
I think you would need to use INDEXPROPERTY to weed them out:
WHERE INDEXPROPERTY ( ID, name, 'IsStatistics') = 0 and INDEXPROPERTY (ID,
name, 'IsHypothetical') = 0
HTH
Kalen Delaney
www.solidqualitylearning.com
"rkusenet" <rkusenet@.yahoo.com> wrote in message
news:4321d67f$0$91790$892e7fe2@.authen.white.readfreenews.net...
> "DXC" <DXC@.discussions.microsoft.com> wrote in message
> news:5F002D25-DB29-49A3-9F35-36E782D1882B@.microsoft.com...
>> How can I find the number of indexes created on a user database (Only
>> clustered and Non-clustered indexes in user tables - not system).
> Select count(*) from sysindexes
> where id in (select id from sysobjects where type = 'U')
> and indid <> 255
> 255 is Text column.
>|||So, is following the correct syntax '
SELECT COUNT(*) FROM sysindexes
WHERE INDEXPROPERTY ( ID, name, 'IsStatistics') = 0 and INDEXPROPERTY (ID,
name, 'IsHypothetical') = 0
Thanks.
"Kalen Delaney" wrote:
> Not quite, in SQL 2000 statistics also take up an indid value in
> sysindexes, so you would need to eliminate them also.
> I think you would need to use INDEXPROPERTY to weed them out:
> WHERE INDEXPROPERTY ( ID, name, 'IsStatistics') = 0 and INDEXPROPERTY (ID,
> name, 'IsHypothetical') = 0
> HTH
> Kalen Delaney
> www.solidqualitylearning.com
>
> "rkusenet" <rkusenet@.yahoo.com> wrote in message
> news:4321d67f$0$91790$892e7fe2@.authen.white.readfreenews.net...
> >
> > "DXC" <DXC@.discussions.microsoft.com> wrote in message
> > news:5F002D25-DB29-49A3-9F35-36E782D1882B@.microsoft.com...
> >> How can I find the number of indexes created on a user database (Only
> >> clustered and Non-clustered indexes in user tables - not system).
> >>
> >
> > Select count(*) from sysindexes
> > where id in (select id from sysobjects where type = 'U')
> > and indid <> 255
> >
> > 255 is Text column.
> >
> >
>
>|||what does 'IsHypothetical' mean?|||Hypothetical indexes hold column level statistics. They are created by SQL
Server and used internally. They cannot be used directly as a data access
path.
--
Gail Erickson [MS]
SQL Server Documentation Team
This posting is provided "AS IS" with no warranties, and confers no rights
<ford_desperado@.yahoo.com> wrote in message
news:1126296886.681664.90670@.g47g2000cwa.googlegroups.com...
> what does 'IsHypothetical' mean?
>|||Did you try it? If you have a syntax error you will be told so.
You will also need the other conditions in the WHERE clause, not just these
two.
HTH
Kalen Delaney
"DXC" <DXC@.discussions.microsoft.com> wrote in message
news:700341BA-4C8D-4BE5-9970-DFC1151E6DBB@.microsoft.com...
> So, is following the correct syntax '
> SELECT COUNT(*) FROM sysindexes
> WHERE INDEXPROPERTY ( ID, name, 'IsStatistics') = 0 and INDEXPROPERTY
> (ID,
> name, 'IsHypothetical') = 0
> Thanks.
> "Kalen Delaney" wrote:
>> Not quite, in SQL 2000 statistics also take up an indid value in
>> sysindexes, so you would need to eliminate them also.
>> I think you would need to use INDEXPROPERTY to weed them out:
>> WHERE INDEXPROPERTY ( ID, name, 'IsStatistics') = 0 and INDEXPROPERTY
>> (ID,
>> name, 'IsHypothetical') = 0
>> HTH
>> Kalen Delaney
>> www.solidqualitylearning.com
>>
>> "rkusenet" <rkusenet@.yahoo.com> wrote in message
>> news:4321d67f$0$91790$892e7fe2@.authen.white.readfreenews.net...
>> >
>> > "DXC" <DXC@.discussions.microsoft.com> wrote in message
>> > news:5F002D25-DB29-49A3-9F35-36E782D1882B@.microsoft.com...
>> >> How can I find the number of indexes created on a user database (Only
>> >> clustered and Non-clustered indexes in user tables - not system).
>> >>
>> >
>> > Select count(*) from sysindexes
>> > where id in (select id from sysobjects where type = 'U')
>> > and indid <> 255
>> >
>> > 255 is Text column.
>> >
>> >
>>
>>
>|||An index created by the Index Tuning Wizard; these are usually removed when
the IDW is done, but sometimes they manage to stick around.
HTH
Kalen Delaney
<ford_desperado@.yahoo.com> wrote in message
news:1126296886.681664.90670@.g47g2000cwa.googlegroups.com...
> what does 'IsHypothetical' mean?
>