declare @dt as datetime
set @dt= getdate()-01 -- дата, меньше которой будем обновлять
begin try
select o.name as [objname],s.name as [shname],o.object_id,st.name as [stname]
into #t_stat
from sys.stats st
inner join sys.objects o on st.object_id=o.object_id
inner join sys.schemas s on o.schema_id=s.schema_id
where o.type='U'
and STATS_DATE(st.object_id, st.stats_id) <@dt --фильтр по дате
--and o.name='dbservers' --фильтра по обеъкту
end try
begin catch
print (cast(error_number() as nchar(5))+'_'+ ERROR_MESSAGE())
drop table #t_stat
end catch
--2 открываем курсом и обновляем статистику
declare cur1 cursor
for select objname,shname,stname from #t_stat
open cur1
declare @stname sysname
declare @obj sysname
declare @sh sysname
fetch next from cur1 into @obj,@sh,@stname
WHILE (@@FETCH_STATUS <> -1)
BEGIN
execute ('update statistics ['+@sh+'].['+@obj+'] '+@stname +' WITH FULLSCAN ')
print ('update statistics ['+@sh+'].['+@obj+'] ['+@stname +'] WITH FULLSCAN ')
fetch next from cur1 into @obj,@sh,@stname
end
close cur1
DEALLOCATE cur1
--select * from #t_stat
drop table #t_stat
GO
DBCC FLUSHPROCINDB(6)
SELECT DB_ID() AS [Database ID]; -- узнать дб ид у базы
GO
Similar Posts: