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
go

select

s.name as 'Schema',
a.name as 'Table',
a.type_desc as 'Table Type',
i.name 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 s.name asc, a.name asc, i.type_desc asc

 

 

Quick, easy to use, and to the point!

Tags in this Article