declare @DBName Varchar(50)
declare @SQL Varchar(2000)
declare MyCursor Cursor for
SELECT RTRIM(m.name)SysDBName
from master..sysdatabases m
where m.status not in (512, 528)
and m.name not like 'temp%'
and m.dbid > 4
Open MyCursor
Fetch next from MyCursor into @DBName
while @@FETCH_STATUS = 0
begin
Set @SQL = 'Use [' + @DBName + ']; EXEC sp_updatestats;'
exec(@SQL)
Fetch next from MyCursor into @DBName
End
Close MyCursor
deallocate MyCursor
1 則留言:
--Output update stats only for outdated:
select'UPDATE STATISTICS ' + S.name + '.' + O.name + ' WITH FULLSCAN ;' as IndexTableName
from (
SELECT ST.object_id
--, ST.name AS stats_name,
--ISNULL(STATS_DATE(object_id, stats_id),0) AS statistics_update_date
FROM sys.stats ST
Where DateDiff(DAY,ISNULL(STATS_DATE(object_id, stats_id),0), GETDATE()-7) > 7
Union
Select I.object_id
--, I.name AS index_name,
-- ISNULL(STATS_DATE(I.object_id, index_id),0) AS statistics_update_date
FROM sys.indexes I
Where DateDiff(DAY,ISNULL(STATS_DATE(I.object_id, index_id),0), GETDATE()-7) > 7) Index_Stats
Inner Join sys.objects O
ON Index_Stats.object_id = O.object_id
AND O.type = 'U'
Inner join sys.schemas S
ON O.schema_id = S.schema_id
AND S.name = 'DBO'
張貼留言