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
Read more: BrianKMcDonald
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
Read more: BrianKMcDonald