Tony’s Weblog

ASP.NET, MVC, AJAX, SilverLight, C#, WCF Web Service, SQL 2005 and other interesting stuff.

When do we use the UPDATE_STATISTICS command?

Posted by Tony on August 2, 2008

This command is basically used when a large processing of data has occurred. If a large amount of deletions any modification or Bulk Copy into the tables has occurred, it has to update the indexes to take these changes into account. UPDATE_STATISTICS updates the indexes on these tables accordingly.

The following stored procedure can be used to run the “Update_Statistic” command on all the tables in your database:


create procedure Update_Statistics

set nocount on

declare @tablename varchar(150)

declare table_name cursor for
select ‘UPDATE STATISTICS ‘ + name from sysobjects where type = ‘u’
order by name

open table_name

fetch next from table_name
into @tablename


exec (@tablename)
print @tablename
FETCH NEXT FROM table_name INTO @tablename
print ”
print ‘All user table statistics have been updated’

CLOSE table_name
DEALLOCATE table_name


Leave a Reply

Please log in using one of these methods to post your comment: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s

%d bloggers like this: