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

statistics Img SQL SERVER   Find Details for Statistics of Whole Database   DMV   T SQL ScriptI 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);

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

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)

15 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

  8. Hi Pinal
    I had a need to script out stats from rather a lot of databases with many tables, and couldn’t see an easy way to do this other than generate scripts and then trimming out all of the stuff I didn’t need.
    I googled and while there are scripts to do this for indexes, there are non around for stats.
    On finding you post I decided that spending the time to create such a script using yours as a basis would be better than doing the above generate scripts and and trim out the unneeded stuff.
    I thought I would share the script here for the benefit of anyone else who might ever need to do the same thing.
    I’m sure there are further tweaks that can be made but this suited the needs we had.

    DECLARE @script AS VARCHAR(2000),
    @tableName2 AS VARCHAR(100),
    @statName AS VARCHAR(100),
    @column AS VARCHAR(100),
    @last AS BIT,
    @dbName2 AS VARCHAR(100)
    DECLARE StatsCursor CURSOR FOR
    SELECT DISTINCT
    ‘DROP STATISTICS [‘ + sch.name + ‘].[‘ + obj.name +’].[‘ + S.name + ‘]’ AS Script
    FROM sys.stats s
    JOIN sys.partitions par ON par.[object_id] = s.[object_id]
    JOIN sys.objects obj ON par.[object_id] = obj.[object_id]
    JOIN sys.schemas sch ON obj.schema_id = sch.schema_id
    WHERE OBJECTPROPERTY(s.OBJECT_ID,’IsUserTable’) = 1
    AND (s.user_created = 1)
    OPEN StatsCursor
    FETCH NEXT FROM StatsCursor INTO @script
    SET @dbName2 = (SELECT DB_NAME())
    PRINT ‘–SERVER: ‘ + @@SERVERNAME
    PRINT ‘USE ‘ + @dbName2
    PRINT ‘GO’
    PRINT ”
    PRINT ‘———- Drop STATS ———-‘
    WHILE @@FETCH_STATUS = 0
    BEGIN
    PRINT @script
    PRINT ‘GO’
    FETCH NEXT FROM StatsCursor INTO @script
    END
    CLOSE StatsCursor
    DEALLOCATE StatsCursor
    PRINT ”
    PRINT ‘———- Create STATS ———-‘
    DECLARE StatsCursor CURSOR FOR
    SELECT DISTINCT
    ‘CREATE STATISTICS [‘ + S.name + ‘] ON [‘ + sch.name + ‘].[‘ + obj.name +’]’ AS Script,
    OBJECT_NAME(s.[object_id]) AS TableName,
    s.name AS StatName
    FROM sys.stats s
    JOIN sys.partitions par ON par.[object_id] = s.[object_id]
    JOIN sys.objects obj ON par.[object_id] = obj.[object_id]
    JOIN sys.schemas sch ON obj.schema_id = sch.schema_id
    WHERE OBJECTPROPERTY(s.OBJECT_ID,’IsUserTable’) = 1
    AND (s.user_created = 1)
    ORDER BY Statname, TableName
    OPEN StatsCursor
    FETCH NEXT FROM StatsCursor INTO @script, @tableName2, @statName
    WHILE @@FETCH_STATUS = 0
    BEGIN
    SET @script = @script + ‘(‘
    SET @last = 1
    DECLARE ColumnCursor CURSOR FOR
    SELECT
    c.name
    FROM
    sys.stats s JOIN
    sys.stats_columns sc JOIN sys.columns c ON c.[object_id] = sc.[object_id] AND c.column_id = sc.column_id
    ON sc.[object_id] = s.[object_id]
    AND sc.stats_id = s.stats_id JOIN sys.objects o ON c.[object_id] = o.[object_id]
    WHERE
    s.name = @statName AND o.[name] = @tableName2
    ORDER BY stats_column_id
    OPEN ColumnCursor
    FETCH NEXT FROM ColumnCursor INTO @column
    WHILE @@FETCH_STATUS = 0
    BEGIN
    SET @script = @script + ‘[‘ + @column + ‘],’
    FETCH NEXT FROM ColumnCursor INTO @column
    END
    CLOSE ColumnCursor
    DEALLOCATE ColumnCursor
    SET @script = LEFT(@script,LEN(@script)-1)
    SET @script = @script + ‘)’
    PRINT @script
    PRINT ‘GO’
    FETCH NEXT FROM StatsCursor INTO @script, @tableName2, @statName
    END
    CLOSE StatsCursor
    DEALLOCATE StatsCursor

    Like

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