I'm still playing with this and trying to learn.

The following codeselect distinct db_name(db_id()) DbName,

so.name as 'TableName',ISNULL(si.name,'No Index') as IndexName,

si.index_id,Case When is_primary_key=1 then 'Primary Key Constraint'

Else 'Index' End ConstraintType, si.type_desc,

dbo.udf_GetIndexCol(si.index_id,so.object_id,0) As IndexKeyColumn,

dbo.udf_GetIndexCol(si.index_id,so.object_id,1) As IncludedCols,

spi.user_seeks,

spi.user_scans,spi.user_lookups,

spi.user_updates,

(user_seeks+user_scans+user_lookups+user_updates) as 'IndexUsage ',

dbo.udf_GetIndexsize(si.index_id,so.object_id) as 'IndexSizeKB',Cast(

(user_seeks+user_scans+user_lookups+user_updates)/

dbo.udf_GetIndexSize(si.index_id,so.object_id) As decimal(10,2)) As IndexUsagetoSizeRatio from sys.objects so inner join sys.indexes si

on so.object_id=si.Object_id inner join sys.dm_db_index_usage_stats spi

on spi.Object_id=so.Object_id and spi.index_id=si.index_id inner join sys.index_columns sic

on sic.object_id=si.object_id and sic.index_id=si.index_id inner join sys.columns sc

on sc.Column_id=sic.column_id and sc.object_id=sic.object_id inner join INFORMATION_SCHEMA.TABLE_CONSTRAINTS c

on so.name=c.TABLE_NAME where so.type='u'

which is used in the initial IndexUsageInfo sp seems to only pull through details on tables which have a Primary Key. I have a whole host of tables which don't have PK's set but do have some indexing, and yet they're not appearing. Why?

Edit: - I've just spotted it. if changing the inner join INFORMATION_SCHEMA.TABLE_CONSTRAINTS to LEFT JOIN, it returns all indexes.