SQL SERVER – Find Details for Statistics of Whole Database – DMV – T-SQL Script

I was recently asked is there a single script which can provide all the necessary details about statistics for any database. This question made me write following script. I was initially planning to use sp_helpstats command but I remembered that this is marked to be deprecated in future. Again, using DMV is the right thing to do moving forward. I quickly wrote following script which gives a lot more information than sp_helpstats.

USE AdventureWorks
GO
SELECT DISTINCT
OBJECT_NAME(s.[object_id]) AS TableName,
c.name AS ColumnName,
s.name AS StatName,
s.auto_created,
s.user_created,
s.no_recompute,
s.[object_id],
s.stats_id,
sc.stats_column_id,
sc.column_id,
STATS_DATE(s.[object_id], s.stats_id) AS LastUpdated
FROM sys.stats s JOIN sys.stats_columns sc ON sc.[object_id] = s.[object_id] AND sc.stats_id = s.stats_id
JOIN sys.columns c ON c.[object_id] = sc.[object_id] AND c.column_id = sc.column_id
JOIN sys.partitions par ON par.[object_id] = s.[object_id]
JOIN sys.objects obj ON par.[object_id] = obj.[object_id]
WHERE OBJECTPROPERTY(s.OBJECT_ID,'IsUserTable') = 1
AND (s.auto_created = 1 OR s.user_created = 1);

If you have better script to retrieve information about statistics, please share here and I will publish it with due credit.

Update: Read follow up excellent blog post by Jason Brimhall.

Reference: Pinal Dave (http://blog.SQLAuthority.com)

About these ads

14 thoughts on “SQL SERVER – Find Details for Statistics of Whole Database – DMV – T-SQL Script

  1. Hi,

    Very interesting article.

    For people that don’t know much about DMVs, they can discover a lot more about improving SQL performance via DMVs in this new book http://www.amazon.com/gp/product/1935182730/ It contains more than 100 scripts to identify problems, and offers a wide range of solutions.

    Chapters 1 and 3 can be downloaded for free at http://www.manning.com/stirk

    Chapter 1 includes scripts for:

    A simple monitor
    Finding your slowest queries
    Find your missing indexes
    Identifying what SQL is running now
    Quickly find a cached plan

    Thanks
    Ian

    Like

  2. Hi Pinal,

    Your query gives a whole lot of information about statistics.

    The one which i was using always was

    sp_msforeachtable “sp_autostats ‘?'”
    This wont give all the info that your script gives, but most of the time I am only interested in knowing last updated and whether auto stats is enabled.

    Thanks for the script , it is going to be handy.

    Like

  3. Hi Pinal,

    I use slightly different query using sys.index – thought to share here.

    SELECT o.name AS tablename ,
    i.name AS indexname ,
    i.type_desc AS indextype ,
    STATS_DATE(i.[object_id], i.index_id) AS statisticsdate
    FROM sys.indexes i
    INNER JOIN sys.objects o
    ON i.[object_id] = o.[object_id]
    WHERE o.TYPE = ‘U’ –Only get indexes for User Created Tables
    AND i.name IS NOT NULL
    ORDER BY o.name ,i.TYPE

    It doesn’t give the column name and the other details though – like yours.

    – Regards
    Sabya

    Like

  4. Hello pinal ….i’m in love with ur blog….i’m a new database admin and learning very fast from ur blog…..hope to help other newbies one day.

    Regards
    Wakil

    Like

    • hey i am new too. i saw your comment on a blog site. i was wondering where you got your training at. what videos or websites or examples did you use to learn experience/
      Thank you

      Like

  5. @Amir, and others, you could start with this and then build it up as you desire:

    select c.name, o.name, t.name from syscolumns c
    INNER JOIN sysobjects o on c.id = o.id and o.xtype = ‘U’
    INNER JOIN systypes t on c.xusertype = t.xusertype
    order by o.name, c.name

    Like

  6. Pinal
    I started with your query and changed it a bit. I do not know why you join to sys.partitions.Instead of sys.objects I use sys.tables.
    SELECT
    t.name AS TableName,
    c.name AS ColumnName,
    s.name AS StatName,
    STATS_DATE(s.[object_id], s.stats_id) AS LastUpdated,
    s.has_filter,
    s.filter_definition,
    s.auto_created,
    s.user_created,
    s.no_recompute
    FROM sys.stats s
    JOIN sys.stats_columns sc ON sc.[object_id] = s.[object_id] AND sc.stats_id = s.stats_id
    JOIN sys.columns c ON c.[object_id] = sc.[object_id] AND c.column_id = sc.column_id
    JOIN sys.tables t ON s.[object_id] = t.[object_id]
    WHERE t.is_ms_shipped = 0
    ORDER BY t.name,s.name,c.name;

    Like

  7. Pingback: SQL SERVER – Weekly Series – Memory Lane – #035 | Journey to SQL Authority with Pinal Dave

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s