How to aggregate the number of indexes on a table and columns per index I have a simple query that returns every table, index and the columns of the index in a database. I'd like to add two aggregate columns that return the number of indexes on a table and then the amount of columns on each index.

I'm looking to build a report of every database on a server, each table in there and each index and column to look for tables with too many indexes and large indexes with more than 2-3 key columns.

 SELECT 
 @@SERVERNAME as server_name
,DB_NAME() as database_name
 ,TableName = t.name
 ,IndexName = ind.name
 ,ColumnName = col.name
 ,ic.key_ordinal
 ,ind.is_disabled
 ,IndexId = ind.index_id
 ,ColumnId = ic.index_column_id
 FROM 
 sys.indexes ind 
 INNER JOIN sys.index_columns ic ON  ind.object_id = ic.object_id and 
 ind.index_id = ic.index_id 
  INNER JOIN sys.columns col ON ic.object_id = col.object_id and 
  ic.column_id = col.column_id 
  INNER JOIN sys.tables t ON ind.object_id = t.object_id 
  WHERE t.is_ms_shipped = 0 
  ORDER BY database_name, t.name, ind.name, ind.index_id
If you want a stable, reliable, and free script, try sp_BlitzIndex. As a disclaimer, it's part of an open source project I contribute to.

If you run it like so, it will go through all the databases on your server and produce the information you asked for, plus a lot of other nice details.

EXEC sp_BlitzIndex @GetAllDatabases = 1, @Mode = 2;