Printed 2017/07/22 05:37AM

Return Indexes for a Particular Table using TSQL

By Brian K. McDonald, 2010/10/16

I posted a blog recently about returning a listing of all indexes for database, which is good if you want to see how many are in a database. However, you may want to return the indexes for one particular table. Script 1 shown below will do just that!  Just modify the table that you are looking for (and USE statement like above) and execute the script. See figure 1 for sample results of executing this script against the AdventureWorksDW2008R2 database available on Codeplex.

Script 1: List Indexes for Specific Table

USE AdventureWorksDW2008R2




   AS TableName

            , 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 IS NOT NULL

            AND = 'FactInternetSales'


  , si.type 

Figure 1: Sample Results – Specified Table


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: | Blogs: SSRSGeek | SQLServerCentral | BIDN

Twitter: @briankmcdonald | LinkedIn:

Copyright © 2002-2017 Redgate. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.