SQL SERVER – Find Details for Statistics of Whole Database

SQL SERVER - Find Details for Statistics of Whole Database stats-500x496

I was recently asked if there’s a single script that can provide all the necessary details about statistics for any database. This question prompted me to write the following script. Initially, I considered using the sp_helpstats command, but I remembered that it is marked for deprecation in future versions of SQL Server. As best practice, using Dynamic Management Views (DMVs) is the right approach moving forward.

With this in mind, I quickly wrote the following script, which provides more comprehensive information than sp_helpstats ever could.

-- 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 stats_DMV

This script provides valuable insights into various statistics, such as:

  1. Table Name: Displays the name of the table associated with the statistics.
  2. Column Name: Identifies the column that the statistic is created on.
  3. Stat Name: Lists the name of the statistic.
  4. Auto-Created or User-Created: Helps distinguish between automatically generated statistics and user-created ones.
  5. Last Updated: The timestamp of the last update to the statistic.

By using this script, you gain a more detailed understanding of the statistics in your SQL Server database, ensuring that you can manage performance and query optimization more effectively.

If you have a better script or suggestions for improvements, please feel free to share it here. I will be happy to publish it with due credit.

If you need further assistance with this script or have any other questions, feel free to leave a comment or send me an email at my contact address.

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

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