Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
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

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

ORDER BY

            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

 

 

Comments

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.

Leave a Comment

Please register or log in to leave a comment.