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
so.name AS TableName
, si.name AS IndexName
, si.type_desc AS IndexType
JOIN sys.objects so ON si.[object_id] = so.[object_id]
so.type = 'U' --Only get indexes for User Created Tables
AND si.name IS NOT NULL
AND so.name = 'FactInternetSales'
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: firstname.lastname@example.org | Blogs: SSRSGeek | SQLServerCentral | BIDN
Twitter: @briankmcdonald | LinkedIn: http://tinyurl.com/BrianKMcDonald