Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.
Sign up to join this community
I am trying to write a sproc to maintain all the indexes created by me.
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.
This is a way to select all the indexes of user defined tables
select
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.
