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