This article is half-done without your Comment! *** Please share your thoughts via Comment ***
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.
1 2 3 4 5 6 7 8 9 10 11 |
SELECT 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; |
Leave a Reply