/*
* Rhys Jones, 7th Feb 2008
*
* Update stats in indexed views because indexed view stats are not updated by sp_updatestats.
* Only does an update if rowmodctr is non-zero.
*
* No error handling, doesn't deal with disabled clustered indexes.
* Doesn't respect existing sample rate.
* sys.sysindexes.rowmodctr is not completely reliable in SQL Server 2005.
*
*/
DECLARE @view_name NVARCHAR(1024)
DECLARE @sql NVARCHAR(MAX)
PRINT N'Updating stats for indexed views in ' + DB_NAME() + N' . . .'
DECLARE cur CURSOR LOCAL FAST_FORWARD FOR
SELECT
QUOTENAME(ss.name, N'[') + N'.' + QUOTENAME(so.name, N'[') AS view_name
--MAX(ssi.rowmodctr) AS max_rowmodctr,
--MAX(DATEDIFF(hh, STATS_DATE(so.object_id, si.index_id), GETDATE())) AS max_age_hours
FROM
sys.objects so
INNER JOIN sys.schemas ss ON ss.schema_id = so.schema_id
INNER JOIN sys.indexes si ON si.object_id = so.object_id
INNER JOIN sys.sysindexes ssi ON ssi.id = si.object_id
AND ssi.indid = si.index_id
WHERE
so.type = 'V'
GROUP BY
QUOTENAME(ss.name, N'[') + N'.' + QUOTENAME(so.name, N'[')
HAVING
MAX(ssi.rowmodctr) > 0
OPEN cur
FETCH NEXT FROM cur INTO @view_name
WHILE (@@fetch_status = 0)
BEGIN
PRINT N' Updating stats for view ' + @view_name
SET @sql = N'UPDATE STATISTICS ' + @view_name
EXEC (@sql)
FETCH NEXT FROM cur INTO @view_name
END
CLOSE cur
DEALLOCATE cur
PRINT N'Finished updating stats for indexed views in ' + DB_NAME() + N'.'
GO