Technical Article

Update statistics for all tables in any DB

,

Most of the time even after executing dbcc dbreindex script on database you won't see much improvement in application/DB performance.  Thats because dbreindex creates statistics for all tables but it executes sp_updatestats which is like sample statistics.

To get the maximum performance we had to execute Update statistics with fullscan on table. This simple will update statistics of all tables in any given database.

USE pubs -- Change desired database name here
GO
SET NOCOUNT ON
GO
DECLARE updatestats CURSOR FOR
SELECT table_name FROM information_schema.tables
where TABLE_TYPE = 'BASE TABLE'
OPEN updatestats

DECLARE @tablename NVARCHAR(128)
DECLARE @Statement NVARCHAR(300)

FETCH NEXT FROM updatestats INTO @tablename
WHILE (@@FETCH_STATUS = 0)
BEGIN
   PRINT N'UPDATING STATISTICS ' + @tablename
   SET @Statement = 'UPDATE STATISTICS '  + @tablename + '  WITH FULLSCAN'
   EXEC sp_executesql @Statement
   FETCH NEXT FROM updatestats INTO @tablename
END

CLOSE updatestats
DEALLOCATE updatestats
GO
SET NOCOUNT OFF
GO

Rate

4.75 (8)

You rated this post out of 5. Change rating

Share

Share

Rate

4.75 (8)

You rated this post out of 5. Change rating