Skip to main content

Finding all SQL Indexes

Author by Nick Adams

In some cases, trying to find all of your indexes that you want to use in your database can be cumbersome and time consuming if you are going to use the GUI.  This quick and easy query will help you find your indexes in the respective database and what table they are on.  You can also modify this code slightly to narrow down you records.


use --your_database

select as 'Schema', as 'Table',
a.type_desc as 'Table Type', as 'Index Name',
i.type_desc as 'Index Type',
i.index_id as 'Index ID'

from sys.all_objects a
inner join sys.schemas s on a.schema_id = s.schema_id
inner join sys.indexes i on a.object_id = i.object_id

where i.type_desc <> N'HEAP'

order by asc, asc, i.type_desc asc



Quick, easy to use, and to the point!

Tags in this Article