This script will list all Indexes and their types (clustered/nonclustered).
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 ORDER BY so.name, si.type
This post first appeared on Random Technical Thoughts | Things I Want To Remem, please read the originial post: here