LearnItFirst User Forum

/*
    Author: Scott Whigham from http://www.LearnSqlServer.com/

    Description: This script returns the number of clustered and nonclustered indexes per table
   
    Versions: SQL Server 2005
        -- For SQL Server 2000, just change it to sysindexes and i.object_id to i.id
   
    Creation Date: Sept 12, 2006

    For more scripts like this one, visit http://forums.learnsqlserver.com/codesamples.aspx
*/
SELECT OBJECT_NAME( i.object_id ) AS TableName
    , COUNT (*) AS NumberOfIndexes
    , ( SELECT MAX(row_count) FROM sys.dm_db_partition_stats WHERE object_id = i.object_id )
        AS NumberOfRows
FROM sys.indexes AS i
WHERE OBJECTPROPERTY( i.object_id , 'IsTable' ) = 1
    AND OBJECTPROPERTY( i.object_id , 'IsMSShipped' ) = 0
GROUP BY i.object_id
ORDER BY NumberOfIndexes DESC, TableName
GO