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)