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

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