here is a great article on the topic along with some sql code to show how. i'm gonna put a copy of the code here just in case that blog entry ever goes away. enjoy!

if object_id('dbo.fn_indexColumnList') > 0
drop function dbo.fn_indexColumnList
go

create function dbo.fn_indexColumnList(@objectId int, @indexId int)
returns nvarchar(max)
as
/*
Returns a text-based list of column names, in key order, for the object/index combination passed
*/
begin
declare @colList nvarchar(max);
set @colList = N'';

-- First, get just the key columns...
select @colList = @colList + case when len(@colList) > 0 then ',' else '' end + c.name
from sys.index_columns ic with(nolock)
join sys.columns c
on ic.object_id = c.object_id
and ic.column_id = c.column_id
where ic.object_id = @objectId
and ic.index_id = @indexId
and ic.key_ordinal > 0
and ic.is_included_column = 0
order by ic.key_ordinal;

-- Now append any included columns...
if exists(select * from sys.index_columns where object_id = @objectId and index_id = @indexId and is_included_column > 0) begin
set @colList = @colList + ' (';

select @colList = @colList + c.name + ','
from sys.index_columns ic with(nolock)
join sys.columns c
on ic.object_id = c.object_id
and ic.column_id = c.column_id
where ic.object_id = @objectId
and ic.index_id = @indexId
and ic.is_included_column > 0;

set @colList = @colList + '$$^^$$';
set @colList = replace(@colList,',$$^^$$',')');
end

return @colList;
end
go

use master
go

if ((object_id('sp_indexUsageInfo') is not null) and (objectproperty(object_id('sp_indexUsageInfo'), 'IsProcedure') = 1))
drop proc [dbo].sp_indexUsageInfo
go

create proc [dbo].sp_indexUsageInfo
@tableName nvarchar(255) = null, -- Name of a specific table/view/object to retrieve index usage information for - if null/default/0, no specific table filter is used
@rowcount int = null, -- Value to limit the result set to (top x) - if not passed, all data is returned
@opts int = 0 -- Bit flags that indicate what to return for the procedure call:
-- 1 bit - if set, order of results will be descending instead of ascending (by default, we order showing least used first up to most used,
-- if this bit is set, we do the opposite instead...
-- 2 bit - if set, whatever sort order is used will be applied to system values instead of user values
as

/*

NOTE: Use of this procedure requires the existence of the following procedures/functions as well:
1. dbo.fn_indexColumnList()

-- Show all structures for the current db, in order of least user-based uses and then largest rowCount
exec dbo.sp_indexUsageInfo default, default, default
-- Only top 25
exec dbo.sp_indexUsageInfo default, 25, default

-- Show all structures for the current db, in order of most user-based uses and then largest rowCount
exec dbo.sp_indexUsageInfo default, default, 1

-- Show all structures for the current db, in order of least system-based uses and then largest rowCount
exec dbo.sp_indexUsageInfo default, default, 2

-- Show all structures for the current db, in order of most system-based uses and then largest rowCount
exec dbo.sp_indexUsageInfo default, default, 3

-- Show all structures for the current db, for a single tabled called 'tblBatches', in order of least user-based uses and then largest rowCount
exec dbo.sp_indexUsageInfo 'tblBatches', default, default

-- Show all structures for the current db, for a single tabled called 'tblBatches', in order of most user-based uses and then largest rowCount
exec dbo.sp_indexUsageInfo 'tblBatches', default, 1
*/

set nocount on;
set transaction isolation level read uncommitted;

declare @sql nvarchar(max),
@databaseId int,
@order nvarchar(1000);

-- Format incoming data
select @opts = case when @opts > 0 then @opts else 0 end,
@sql = N'',
@databaseId = db_id(),
@rowcount = case when @rowcount > 0 then @rowcount else 0 end;

select @order = case
when @opts & 3 = 3 then '(u.system_seeks + u.system_scans + u.system_lookups) desc, sizeData.rowCnt desc'
when @opts & 3 = 2 then '(u.system_seeks + u.system_scans + u.system_lookups), sizeData.rowCnt desc'
when @opts & 3 = 1 then '(u.user_seeks + u.user_scans + u.user_lookups) desc, sizeData.rowCnt desc'
when @opts & 3 = 0 then '(u.user_seeks + u.user_scans + u.user_lookups), sizeData.rowCnt desc'
end;

select @sql = @sql + N'
select ' + case when @rowcount > 0 then ' top (@rowcount) ' else '' end +
' object_name(i.object_id) as objectName, isnull(i.name,''HEAP'') as indexName, i.type_desc as indexType,
case when i.type_desc = ''HEAP'' then ''HEAP'' else dbo.fn_indexColumnList(i.object_id, i.index_id) end as columnList,
sizeData.rowCnt as rowCnt, sizeData.totalSpaceMB as totalSpaceMB, sizeData.usedSpaceMB as usedSpaceMB,
u.user_seeks as userSeeks, u.user_scans as userScans, u.user_lookups as userLookups, u.user_updates as userUpdates,
u.system_seeks as sysSeeks, u.system_scans as sysScans, u.system_lookups as sysLookups, u.system_updates as sysUpdates,
datediff(minute,u.last_user_seek,getdate()) as minutesSinceLastUserSeek, datediff(minute,u.last_user_scan,getdate()) as minutesSinceLastUserScan,
datediff(minute,u.last_user_lookup,getdate()) as minutesSinceLastUserLookup, datediff(minute,u.last_user_update,getdate()) as minutesSinceLastUserUpdate,
datediff(minute,u.last_system_seek,getdate()) as minutesSinceLastSystemSeek, datediff(minute,u.last_system_scan,getdate()) as minutesSinceLastSystemScan,
datediff(minute,u.last_system_lookup,getdate()) as minutesSinceLastSystemLookup, datediff(minute,u.last_system_update,getdate()) as minutesSinceLastSystemUpdate
from sys.indexes i
join (
select i.object_id as objectId, i.index_id as indexId,
(sum(a.total_pages) * 8) / 1024 as totalSpaceMB, (sum(a.used_pages) * 8) / 1024 as usedSpaceMB,
(select sum(p2.rows) from sys.partitions p2 with(nolock) where p2.object_id = i.object_id and p2.index_id = i.index_id) as rowCnt
from sys.indexes i
join sys.partitions p
on i.object_id = p.object_id
and i.index_id = p.index_id
join sys.allocation_units a
on p.partition_id = a.container_id ' +
case when len(@tableName) > 0 then ' where i.object_id = object_id(@tableName) ' else '' end +
'group by i.object_id, i.index_id, i.name
) sizeData
on i.object_id = sizeData.objectId
and i.index_id = sizeData.indexId
left join sys.dm_db_index_usage_stats u
on u.object_id = i.object_id
and u.index_id = i.index_id
and u.database_id = @databaseId ' +
case when len(@tableName) > 0 then ' where i.object_id = object_id(@tableName) ' else '' end +
'order by ' + @order;

exec sp_executesql @sql, N'@tableName nvarchar(255), @databaseId int, @rowcount int', @tableName, @databaseId, @rowcount;
go