--Script to show when stats were last updated
--Shows which stats were auto created and which were user created
--02/01/2009
select
object_name(ss.object_id) as TABLE_NAME,
coalesce(si.type_desc,'***NO INDEX***') as INDEX_TYPE,
case si.is_unique when 1 then 'Y' else 'N' end as UNIQUE_INDEX,
case si.is_primary_key when 1 then 'Y' else 'N' end as PRIMARY_KEY,
case si.is_unique_constraint when 1 then 'Y' else 'N' end as UNIQUE_CONSTRAINT,
ss.name as STATISTIC_NAME,
case when si.name is not null then stats_date(t.object_id, si.index_id)
else stats_date(t.object_id, ss.stats_id)
end as LAST_UPDATE_STATS,
stuff((SELECT ', ' + c.name
FROM sys.stats_columns as sc
inner join sys.columns as c
on sc.object_id = c.object_id
and sc.column_id = c.column_id
WHERE ss.object_id = sc.object_id
and ss.stats_id = sc.stats_id
ORDER BY c.column_id FOR XML PATH('')), 1, 1, '') as INDEX_COLUMNS,
case auto_created when 1 then 'Y' else 'N' end as STAT_AUTO_CREATED,
case user_created when 1 then 'Y' else 'N' end as STAT_CREATED_BY_USER
from sys.stats as ss
inner join sys.tables as t
on object_name(ss.object_id) = t.name
left outer join sys.indexes as si
on ss.name = si.name
where t.is_ms_shipped = 0
order by t.name, coalesce(si.index_id, 999)