When do we use the UPDATE_STATISTICS command?

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
as

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

WHILE @@FETCH_STATUS = 0

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

CLOSE table_name
DEALLOCATE table_name