Bulk Administration Tool User Guide, Release 5.2(3)
Update Statistics
Downloads: This chapterpdf (PDF - 151.0KB) | Feedback

Update Statistics

Table Of Contents

Update Statistics


Update Statistics


You need to update statistics on the server after a BAT transaction of more than 3000 records. You can do this by running a batch file on the server. This batch file creates an SQL script and executes it.

Use the following steps to run the batch file:


Step 1 Copy the script to a Notepad file and save it with a .bat extension; for example, updatestatistics.bat.

Step 2 Open a command prompt window and run the file by entering the filename.


The following example shows the contents of the batch file that you need to copy to the Notepad file:

@echo off  
@if "%2x" == "x" goto Usage 
echo DECLARE table_cursor CURSOR FOR >> temp1.sql
echo select Name from SysObjects where xType = 'U'  >> temp1.sql

echo OPEN table_cursor  >> temp1.sql

echo DECLARE @tableName sysname  >> temp1.sql

echo FETCH NEXT FROM table_cursor  >> temp1.sql
echo INTO @tableName  >> temp1.sql

echo WHILE @@FETCH_STATUS = 0  >> temp1.sql
echo  BEGIN  >> temp1.sql
echo 	-- loop through all of the table.  >> temp1.sql
echo 	EXEC( ' UPDATE STATISTICS ' + @tableName + ' WITH FULLSCAN ')  >> 
temp1.sql

echo 	-- Get the next author.  >> temp1.sql
echo 	FETCH NEXT FROM table_cursor  >> temp1.sql
echo 	INTO @tableName >> temp1.sql
echo END >> temp1.sql

osql -S %1 -d %2 -E -e -i temp1.sql

del temp1.sql
goto end
:Usage  
@echo Usage:   UpdateStatistics "server" "database" 
@echo Example: UpdateStatistics . CCM0300 
:end

Note For more information on Update Statistics, see the SQL server help at http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_ua-uz_1mpf.asp.