List all the Non-Clustered Indexes in a SQL Server Database

Here’s a simple script that can list all the Non-Clustered Indexes in a SQL Server 2005/2008 Database

USE AdventureWorks
GO
SELECT systb.name as UserTable,
idx.name as IndexName,
idx.type_desc, idx.is_primary_key
FROM sys.indexes idx, sys.tables systb
WHERE idx.object_id = systb.object_id
and idx.type = 2
ORDER BY UserTable

If you want to avoid doing a join with sys.tables, my colleague and SQL Server MVP Madhivanan suggested an alternate approach that produces the same output. Here it is:

SELECT
object_name(object_id) as UserTable,name as IndexName,
type_desc, is_primary_key from sys.indexes
WHERE type = 2 and objectproperty((object_id),'IsUserTable')=1
ORDER BY UserTable

OUTPUT (Partial)

About The Author

Suprotim Agarwal, MCSD, MCAD, MCDBA, MCSE, is the founder of DotNetCurry, DNC Magazine for Developers, SQLServerCurry and DevCurry. He has also authored a couple of books 51 Recipes using jQuery with ASP.NET Controls and a new one recently at The Absolutely Awesome jQuery CookBook.

Suprotim has received the prestigous Microsoft MVP award for nine times in a row now. In a professional capacity, he is the CEO of A2Z Knowledge Visuals Pvt Ltd, a digital group that represents premium web sites and digital publications comprising of Professional web, windows, mobile and cloud developers, technical managers, and architects.

Get in touch with him on Twitter @suprotimagarwal, LinkedIn or befriend him on Facebook