In this post, I am going to share one script to find a list of auto generated statistics objects of the SQL Server.
When the automatic create statistics option, AUTO_CREATE_STATISTICS, is on, the query optimizer creates statistics on individual columns.
Whenever we are updating the data of statistics objects, it is required to find particular statistics object name of the column.
The auto generated statistics object starts with _WA.
OBJECT_NAME(s.object_id) AS ObjectName
,COL_NAME(sc.object_id, sc.column_id) AS ColumnName
,s.name AS StatisticsName
,STATS_DATE(s.OBJECT_ID,s.stats_id) AS StatisticUpdateDate
FROM sys.stats AS s
JOIN sys.stats_columns AS sc
ON s.stats_id = sc.stats_id
AND s.object_id = sc.object_id
WHERE s.name like '_WA%'
ORDER BY s.name;
Please share your ideas and opinions about this topic with me, your contribution will add true value to this topic.
If anyone has doubts on this topic then please do let me know by leaving comments or send me an email.
If you like this post, then please share it with others.
Please follow dbrnd.com, I will share my experience towards the success of Database Research and Development Activity.
I put up a post every day, please keep reading and learning.
Discover Yourself, Happy Blogging !
Anvesh M. Patel.