added your special columns to my table for this so that it now looks like
USE [Performance]
GO
/****** Object: Table [dbo].[perf_index_usage_stats] Script Date: 06/16/2011 15:41:13 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[perf_index_usage_stats](
[server_name] [nvarchar](128) NULL,
[date_read] [datetime] NULL,
[database_id] [smallint] NULL,
[database_name] [nvarchar](128) NULL,
[table_name] [nvarchar](128) NULL,
[index_id] [int] NULL,
[index_name] [nvarchar](128) NULL,
[user_seeks] [bigint] NULL,
[user_scans] [bigint] NULL,
[user_lookups] [bigint] NULL,
[user_updates] [bigint] NULL,
[last_user_seek] [datetime] NULL,
[last_user_scan] [datetime] NULL,
[last_user_lookup] [datetime] NULL,
[last_user_update] [datetime] NULL,
[system_seeks] [bigint] NULL,
[system_scans] [bigint] NULL,
[system_lookups] [bigint] NULL,
[system_updates] [bigint] NULL,
[last_system_seek] [datetime] NULL,
[last_system_scan] [datetime] NULL,
[last_system_lookup] [datetime] NULL,
[last_system_update] [datetime] NULL,
[pk_id] [bigint] IDENTITY(1,1) NOT NULL,
[IndexUsage] [int] NULL,
[IndexSizeKB] [int] NULL,
[IndexUSageToSizeRatio] [decimal](10, 2) NULL,
[ConstraintType] [varchar](25) NULL,
[IndexKeyColumn] [varchar](1000) NULL,
[IncludedColumn] [varchar](1000) NULL,
[type_desc] [nvarchar](60) NULL,
CONSTRAINT [PK_perf_index_usage_stats] PRIMARY KEY NONCLUSTERED
(
[pk_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
for the first part of capturing the index usage info instead of using the functions i'm using a single query
select server_name = @@servername, date_read = getdate(), a.database_id, database_name = db_name(), table_name = OBJECT_NAME(a.[object_id]),
a.index_id, c.name as index_name, co.name as column_name,
case when ic.is_included_column = 1 then 'Included Column'
else 'Key Column' end Column_Type,
case when c.is_Primary_key = 1 then 'PK'
else 'I' end Index_Type,
c.type_desc,
(a.user_seeks + a.user_scans + a.user_lookups + a.user_updates) as 'IndexUsage',
a.user_seeks, a.user_scans, a.user_lookups, a.user_updates,
a.last_user_seek, a.last_user_scan, a.last_user_lookup, a.last_user_update,
a.system_seeks, a.system_scans, a.system_lookups, a.system_updates,
a.last_system_seek, a.last_system_scan, a.last_system_lookup, a.last_system_update
from sys.dm_db_index_usage_stats a
inner join sys.indexes c on c.object_id = a.object_id and a.index_id = c.index_id
inner join sys.columns co on co.object_id = c.object_id
inner join sys.index_columns ic on ic.object_id = co.object_id and ic.index_id = c.index_id
inner join sys.objects so on so.object_id = a.object_id
inner join INFORMATION_SCHEMA.TABLE_CONSTRAINTS con
on so.name=con.TABLE_NAME where so.type='u'
and a.database_id in (10)
AND OBJECTPROPERTY(a.[object_id], 'IsMsShipped') = 0
tomorrow i'm going to work in the index size data but instead of the function, i have years of saved data from running sys.dm_db_index_physical_stats either daily or every few days and will use that.
and going to add the significance to my missing index scans to see if i can filter out some more data