Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Return Indexes for a Particular Table using TSQL

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

GO

 

SELECT

            so.name AS TableName

            , si.name AS IndexName

            , si.type_desc AS IndexType

FROM

            sys.indexes si

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

WHERE

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

            AND si.name IS NOT NULL

            AND so.name = 'FactInternetSales'

ORDER BY

            so.name, si.type 

Figure 1: Sample Results – Specified Table

Results

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

Comments

Posted by Anonymous on 16 October 2010

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

Leave a Comment

Please register or log in to leave a comment.