Sunday, September 7, 2014

Find Statistics information using T-SQL Scripts

SELECT
    OBJECT_NAME(s.object_id) As TableNm
  , COL_NAME(s.object_id, sc.column_id) AS ColumnNm
  , s.NAME                              AS StatisticsNm
  , STATS_DATE(s.object_id, s.stats_id) AS LastUpdated
  , s.auto_created
  , s.user_created
  , s.no_recompute
  , s.has_filter
FROM   sys.stats s
       INNER JOIN sys.stats_columns sc
               ON s.stats_id = sc.stats_id
                  AND s.object_id = sc.object_id
       INNER JOIN sys.sysobjects o
               ON o.id = s.object_id
WHERE
  o.type = 'U'
ORDER  BY
  OBJECT_NAME(s.object_id)
  , COL_NAME(s.object_id, sc.column_id)
  , s.NAME