Dm_db_stats_properties - to get statistics properties

Managing statistics is important part for developers. Many times we have seen
improper statistics screwed up the queries and queries which completes in seconds
takes hours to complete.
Monitoring statistics and updating it regularly is important for this aspect.
For this we can use  sys.dm_db_stats_properties which give better insight about
each stats for a table.

This DMV has two arguments

Object_id
Stats_id

Better we should user cross apply it with sys.stats and it will give up
in depth details about the stats.

SELECT s.object_id,

       Object_name (s.object_id),

       s.stats_id,

       s.auto_created,

       s.user_created,

       s.no_recompute,

       s.has_filter,

       s.is_temporary,

       s.is_incremental,

       d.last_updated,

       d.rows,

       d.rows_sampled,

       d.steps,

       d.unfiltered_rows,

       d.modification_counter

FROM   sys.stats s

       JOIN sys.tables t

         ON s.object_id = t.object_id

       CROSS apply sys.Dm_db_stats_properties(s.object_id, s.stats_id) d

WHERE  t.type = 'u'