SQL SERVER – Find Details for Statistics of Whole Database

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);

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://darkslategrey-bat-805937.hostingersite.com), YouTube.