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.