There are a tone of scripts to display your indexes, in different type of form.
At some point, I decided to write my own version, and put it into SSMS -> Optins->Environment->Keyboard ->Query Shortcuts.
CREATE PROCEDURE dbo.sp_SQL_helpIndex
@objname nvarchar(128)
AS
SELECT OBJECT_NAME(ius.object_id) as tblName
,i.name
,STUFF(ca.IndexColumnsNames,1,1,”) as IndexColumns
,STUFF(ia.IncludedColumnsNames,1,1,”) as IncludedColumns
,STATS_DATE(ius.object_id, i.index_id) as Stat_Date
,ius.user_seeks
,ius.user_scans
,ius.user_lookups
,ius.last_user_seek
,ius.last_user_scan
,i.is_unique
,i.is_primary_key
,i.is_unique_constraint
,i.is_disabled
,i.has_filter
–,’ALTER INDEX ‘ + QUOTENAME(i.name) + ‘ ON ‘ + QUOTENAME(OBJECT_SCHEMA_NAME(i.object_id )) + ‘.’+ QUOTENAME(OBJECT_NAME(i.object_id)) + ‘ DISABLE;’ as DisableStmt
–,’DROP INDEX ‘ + QUOTENAME(i.name) + ‘ ON ‘ + QUOTENAME(OBJECT_SCHEMA_NAME(i.object_id )) + ‘.’+ QUOTENAME(OBJECT_NAME(i.object_id)) + ‘;’ as dropStmt
FROM sys.dm_db_index_usage_stats as ius
inner join sys.indexes as i
ON ius.object_id = i.object_id
AND ius.index_id = i.index_id
cross apply
(select ‘, ‘+ c.name +”
from sys.index_columns as ic
inner join sys.columns as c
on c.object_id = ic.object_id
and c.column_id = ic.column_id
where ic.object_id = i.object_id
and ic.index_id = i.index_id
and ic.is_included_column = 0
order by ic.key_ordinal
for xml path(”)
)ca(IndexColumnsNames)
outer apply
(select ‘, ‘+ c.name +”
from sys.index_columns as ic
inner join sys.columns as c
on c.object_id = ic.object_id
and c.column_id = ic.column_id
where ic.object_id = i.object_id
and ic.index_id = i.index_id
and ic.is_included_column = 1
order by ic.index_column_id ASC
for xml path(”)
)ia(IncludedColumnsNames)
WHERE
i.type > 1 — nonclustered index
and database_id = DB_ID(DB_Name()) — database in it
and OBJECT_NAME(ius.object_id) =@objname
ORDER BY OBJECT_NAME(ius.object_id),QUOTENAME(i.name)
go
And then mark it as a system stored procedure:
exec [sys].[sp_MS_marksystemobject] ‘sp_SQL_helpIndex’
GO
ps: please here is a condition on it:
and database_id = DB_ID(DB_Name()) — database in it
S
Sql Server 