Listing Statistics Columns

As a follow up to yesterday’s post, here’s how to get a list of table statistics using DMVs:

1. SELECT Table_Name     = SCHEMA_NAME(st.Schema_id) + '.'
2.                         + OBJECT_NAME(st.object_id)
3.     , stats_name      = ss.name
4.     , [stats_columns] = LEFT(SColumns, LEN(Scolumns) - 1)
5.     , ss.user_created
6.     , ss.auto_created
7.     , ss.filter_definition
8.   FROM sys.stats ss
9.   join sys.tables st
10.     on ss.object_id = st.object_id
11. CROSS APPLY
12.      (SELECT cc.name + ', '
13.         FROM sys.stats_columns sc
14.         JOIN sys.columns cc
15.           on sc.object_id = cc.object_id
16.          and sc.column_id = cc.column_id
17.        WHERE ss.object_id = sc.object_id
18.          and ss.stats_id = sc.stats_id
19.        ORDER BY SC.stats_column_id
20.          FOR XML PATH ('') ) cl (SColumns);

As with the last query, substitute sys.objects for sys.tables if you need to capture information about views.