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.

---