I am trying to write a sproc to maintain all the indexes created by me. enter image description here

My indexes are shown in random numbered rows in sys.indexes table, mixed with system indexes. Now I want to write a select statement to choose only indexes created by me. (like I can do for databases 'where database_id > 4' in sys.databases) Here, index_id has different meaning and is not unique. Can someone help me? Thanks for your time.


2 Answers 2


This is a way to select all the indexes of user defined tables

   idx.[name] as [Index]
from sys.indexes as idx
inner join sys.objects as obj on idx.object_id = obj.object_id
where idx.[name] is not null and obj.[type] = 'u'
order by idx.[name]

Not sure exactly what you are up to, but if you're wanting generic maintenance scripts then you can't go wrong with https://ola.hallengren.com/. I'm sure they could be tailored to your precise requirements.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.