I was searching the net for articles/scripts on unused indexes and before I found yours, I came upon this website that gave a script on unused indexes. I modified it a bit to exclude PK's and my record count was 1222. Oh yes, I was naughty and did not administer my indexes as I should have but the problem I have here is I am not a DBA, I am a developer and there is no DBA here and I have been self training for close to 4 years now so unfortunately my developer work always took precedence to the DBA work and that's not quite so bad because I have put maintenance plans in place to do all sorts of things to protect and check the database. I also get some emails if anything goes wrong but I am worried now because the database has become bloated. Back to my query. I added theother query
below and link
to the website.
select object_schema_name(i.object_id) schemaname, object_name(i.object_id) tablename,
from sys.indexes i
where objectproperty(i.object_id, 'IsUserTable') = 1 and isnull(i.name,'') <> '' and
not exists(select ne.index_id from sys.dm_db_index_usage_stats ne
where ne.object_id = i.object_id and ne.index_id = i.index_id and database_id = db_id()) and
left(i.name,2) <> 'PK'
ORDER BY schemaname, tablename, indexname
Now my problem is this: I ran your query and only got only 109 rows for my database. Wow!!! from 1222 to 109 is a great difference. Won't you please have a look and tell me what the other query do that yours don't.
I can do all things through Christ who strengthens me. - Holy Bible
I am a man of fixed and unbending principles, the first of which is to be flexible at all times. - Everett Mckinley Dirkson (Well, I am trying. - Manie Verster)