|
Home > Archive > MS SQL Server > September 2005 > Index count
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
|
|
|
| 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.
| |
| rkusenet 2005-09-09, 8:23 pm |
|
"DXC" <DXC@discussions.microsoft.com> wrote in message
news:5F002D25-DB29-49A3-9F35- 36E782D1882B@microso
ft.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.
| |
| Kalen Delaney 2005-09-09, 8:23 pm |
|
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$9179
0$892e7fe2@authen.white.readfreenews.net...
>
> "DXC" <DXC@discussions.microsoft.com> wrote in message
> news:5F002D25-DB29-49A3-9F35- 36E782D1882B@microso
ft.com...
>
> 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$9179
0$892e7fe2@authen.white.readfreenews.net...
>
>
>
>
| |
| ford_desperado@yahoo.com 2005-09-09, 8:23 pm |
| what does 'IsHypothetical' mean?
| |
| Gail Erickson [MS] 2005-09-09, 8:23 pm |
| 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?
>
| |
| Kalen Delaney 2005-09-09, 8:23 pm |
|
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@microso
ft.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:
>
>
| |
| Kalen Delaney 2005-09-09, 8:23 pm |
|
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?
>
>
|
|
|
|
|