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
max
rather thanmin
? 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. \$\endgroup\$ – Dawood ibn Kareem Feb 17 '12 at 8:09