SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

List Indexes For Database using TSQL

A while ago, I showed you how to find out what indexes were on a table using the GUI. Today, I am going to show you a quick query to determine the indexes for ALL tables in a database. The results will also show you whether it is a CLUSTERED or NONCLUSTERED index. Script 1 below will return ALL indexes in the AdventureWorksDW2008R2 database (available on Codeplex). If you want to see what indexes are in your environment, just modify the USE statement at the top of the script. Figure 1 shows sample results of executing this query.

Script 1: List All Indexes

USE AdventureWorksDW2008R2




            so.name AS TableName

            , si.name AS IndexName

            , si.type_desc AS IndexType


            sys.indexes si

            JOIN sys.objects so ON si.[object_id] = so.[object_id]


            so.type = 'U'    --Only get indexes for User Created Tables

            AND si.name IS NOT NULL


            so.name, si.type 

Figure 1: Sample Results – All Tables

List All Indexes For Database


I hope that you have enjoyed this quick blog. If you did, please rate it! Also, if you don’t already, please be sure to follow me on twitter at @briankmcdonald. Also note that you can subscribe to an RSS feed of my blogs here.


Until next time, thank you for reading,



Brian K. McDonald, MCDBA, MCSD
Business Intelligence Consultant – Pragmatic Works

Email: bmcdonald@pragmaticworks.com | Blogs: SSRSGeek | SQLServerCentral | BIDN

Twitter: @briankmcdonald | LinkedIn: http://tinyurl.com/BrianKMcDonald




Posted by Anonymous on 14 October 2010

Pingback from  Dew Drop – October 14, 2010 | Alvin Ashcraft's Morning Dew

Posted by Sam Enchill on 14 October 2010

that was nice... simple and straight forward.... no big confusing words for novices like me. thumbs up bro..

Posted by Brian K. McDonald on 25 October 2010

Thanks Samuel. Sometimes short blogs are just what people need (me included). :)

Posted by anbuemb on 21 February 2014

Thank you very much my dear brother. you saved your brother's life? thank you thank you so much.

Posted by John Corkett on 29 June 2015

Brief and to the point exactly what I needed in a rush.

Posted by adpr0401 on 13 August 2015

Short, precise and exactly what I needed.

Thank you

Leave a Comment

Please register or log in to leave a comment.