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.

SQL SERVER - Find Details for Statistics of Whole Database - DMV - T-SQL Script statisticsdetails-800x536

-- Details About Statistics
-- Original Author: Pinal Dave 
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);

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

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

If you need further help with this script, do leave a comment or send me email at my email address.

Reference: Pinal Dave (https://blog.sqlauthority.com)

Quest

SQL DMV, SQL Scripts, SQL Server, SQL Statistics
Previous Post
SQL SERVER – Three Puzzling Questions – Need Your Answer
Next Post
SQL SERVER – Query to Find Duplicate Indexes – Script to Find Redundant Indexes

Related Posts

19 Comments. Leave new

  • Thanks a lot Pinal for the Script…
    I knew only about sp_helpstats….

    Reply
  • 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.

    Reply
  • 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

    Reply
  • 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

    Reply
    • 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

      Reply
  • Vijayakumar.P
    August 1, 2011 5:07 pm

    Nice pinal

    Reply
  • The above script does not work in SQL2000. Any other query I can try in SQL2000 ?

    Reply
  • @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

    Reply
  • Vladimir Sotirov
    June 27, 2012 11:18 pm

    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;

    Reply
  • Is there a way to find out how often a statistic is used? similar to the DMV that allows you to get usage information for a statistic.

    Reply
  • Is there a right time or method to remove auto created statistics ?

    Reply
  • DBCC SHOW_STATISTICS (KRM_ACCR_INT,KRM_ACCR_INT_uidx1) this will rock if we are useing for specific Table index.

    Reply
  • 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

    Reply
  • This query provides more information about that statistics in addition to the table schema. It allows you to be able to determine if a statistic is out of date.

    SELECT ss.name AS SchemaName
    , obj.name AS TableName
    , stat.stats_id
    , stat.name AS StatiscticsName
    , stat.filter_definition
    , sp.last_updated
    , sp.[rows]
    , sp.rows_sampled
    , sp.steps
    , sp.unfiltered_rows
    , sp.modification_counter
    FROM sys.objects AS obj
    INNER JOIN sys.schemas ss ON obj.schema_id = ss.schema_id
    INNER JOIN sys.stats stat ON stat.object_id = obj.object_id
    CROSS APPLY sys.dm_db_stats_properties(stat.object_id, stat.stats_id) AS sp
    WHERE obj.is_ms_shipped = 0
    ORDER BY ss.name, obj.name, stat.name

    Reply
  • Thank you. Saved me a lot of grief when the vendor tried to put the blame on us for creating extra statistics when I could prove that they were all auto created. Squashed that line of thinking real quick! I came up with a query like John’s. I found a web page where MS recommended the modification_counter value be 1000 and use that value along with looking at stale statistics (anything over 7 days for last_updated) along with rows rows_sampled when I rebuild statistics. It is more efficient than rebuilding all statistics every day. I do this to meet a particular vendor’s maintenance requirements. The rows rows_sampled is dependent upon vendor requirements, along with the size and types (heap vs regular) of tables. Statistics for really large tables may be better off with the default smaller row sample rate as they will take less time to rebuild.

    Reply
  • jackson Ragel Corrêa
    September 25, 2019 5:46 pm

    Good Morning.
    No words to thank for the immense help.
    Thank you for sharing this jewel.

    Thank you very much here Brazil.
    Hugs

    Reply
  • Guys, can some one help me in troubleshooting cpu utilization in MSSQL. i am new to MSSQL.

    Reply

Leave a Reply