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:
- Table Name: Displays the name of the table associated with the statistics.
- Column Name: Identifies the column that the statistic is created on.
- Stat Name: Lists the name of the statistic.
- Auto-Created or User-Created: Helps distinguish between automatically generated statistics and user-created ones.
- 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.
19 Comments. Leave new
Thanks a lot Pinal for the Script…
I knew only about sp_helpstats….
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.
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
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
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
Nice pinal
The above script does not work in SQL2000. Any other query I can try in SQL2000 ?
@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
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;
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.
Is there a right time or method to remove auto created statistics ?
DBCC SHOW_STATISTICS (KRM_ACCR_INT,KRM_ACCR_INT_uidx1) this will rock if we are useing for specific Table index.
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
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
This is exactly what I needed. Thank you!
Thanks John.
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.
Good Morning.
No words to thank for the immense help.
Thank you for sharing this jewel.
Thank you very much here Brazil.
Hugs
Guys, can some one help me in troubleshooting cpu utilization in MSSQL. i am new to MSSQL.