Return Indexes for a Particular Table using TSQL

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

Posted via email from .NET Info