/************************************************************************************
Author:  Steve Fibich
Version: 1.0.120515
Script:  Query.Stats_Info.sql
************************************************************************************/
select
OBJECT_SCHEMA_NAME(s.object_id) as [schema_name] 
,OBJECT_NAME(s.object_id) as [object_name]
,s.name as [stats_name]
,CAST((
	select c.name as [name] from sys.stats_columns sc 
	inner join
	sys.columns c
	on
	sc.column_id = c.column_id 
	and
	sc.object_id = c.object_id 
	where
	sc.object_id =s.object_id 
	and
	sc.stats_id =s.stats_id 
	order by sc.stats_column_id asc
	FOR XML RAW('col')
) as XML) as [stats_columns]
,STATS_DATE(s.object_id,s.stats_id) as [stats_date]
,s.filter_definition 
,s.no_recompute 
,case when s.auto_created  = 1 then 'Auto'
	when s.user_created = 1 then 'User'
	else 'Index' 
end as [type]
from
sys.stats s
where
OBJECT_SCHEMA_NAME(s.object_id) not in ('sys')