Statistics updating SQL Query

Question

I have the following SQL statement that I think could be improved in areas (I believe there may be a way to use where over having, but I'm not sure how and I'm sure there's a way to reference the last updated column in the having clause but I'm unsure how to do it). Any suggestions would be greatly appreciated:

/* Show indexes that haven't had statistics updated in two days or more */
select 
 t.name as [Table Name], 
 min(convert(CHAR(11),stats_date(t.object_id,s.[stats_id]),106)) as [Last Updated]
from sys.[stats] as s
 inner join sys.[tables] as t
 on [s].[object_id] = [t].[object_id]
group by t.name
having min(convert(CHAR(11),stats_date(t.object_id,s.[stats_id]),106)) < dateadd(day, -1, getdate())
order by 2 desc

• Dawood ibn Kareem: Don't you want max rather than min? Your comment at the top of the code suggest that you want tables whose latest statistics entry is more than two days ago, not whose earliest statistics entry is in that range.

Answer 1

You can just use the condition in a where, that will filter the single records instead of filtering groups, then you can group the result to avoid duplicates in the result.

You shouldn't convert the date to a string when you are comparing it. When you convert it to a string, you should do that after getting the lowest value, otherwise min will compare the dates as strings and give you the wrong result.

select
 t.name as [Table Name], 
 convert(CHAR(11),min(stats_date(t.object_id,s.[stats_id])),106) as [Last Updated]
from
 sys.[stats] as s
 inner join sys.[tables] as t on s.[object_id] = t.[object_id]
where
 stats_date(t.object_id,s.[stats_id]) < dateadd(day, -1, getdate())
group by
 t.name
order by
 2 desc