BUG: DOC: Update Statistics Example to Update All Tables Wrong

• Microsoft SQL Server versions 6.0, 6.5

SYMPTOMS

The UPDATE STATISTICS Statement article in the Transact-SQL Reference documentation has an error. Syntax is missing the script listed in the section titled "C. Use Cursors to Run UPDATE STATISTICS on All Tables."

If you try to execute this script, the following errors are returned:

Msg 170, Level 15, State 1
Line 18: Incorrect syntax near 'RTRIM'.
Msg 170, Level 15, State 1
Line 20: Incorrect syntax near '@tablename'.
Msg 170, Level 15, State 1
Line 27: Incorrect syntax near ' *************'.
Msg 2812, Level 16, State 4
Stored procedure 'update_all_stats' not found.

WORKAROUND

The script needs to be modified by adding three additional plus signs (+). The script below will run without error:

CREATE PROCEDURE update_all_stats
AS

/*
   This procedure will run UPDATE STATISTICS against
   all user-defined tables within this database.
*/ 

DECLARE @tablename varchar(30)
DECLARE @tablename_header varchar(75)
DECLARE tnames_cursor CURSOR FOR SELECT name FROM sysobjects
   WHERE type = 'U'
OPEN tnames_cursor
FETCH NEXT FROM tnames_cursor INTO @tablename
WHILE (@@fetch_status <> -1)
BEGIN
   IF (@@fetch_status <> -2)
   BEGIN
      SELECT @tablename_header = "Updating "  +
RTRIM(UPPER(@tablename))
      PRINT @tablename_header
      EXEC ("UPDATE STATISTICS " + @tablename )
   END
   FETCH NEXT FROM tnames_cursor INTO @tablename
END
PRINT " "
PRINT " "
SELECT @tablename_header = "*************  NO MORE TABLES" +
 "  *************"
PRINT @tablename_header
PRINT " "
PRINT "Statistics have been updated for all tables."
DEALLOCATE tnames_cursor
go
update_all_stats
go 

STATUS

Microsoft has confirmed this to be a problem in Microsoft SQL Server version 6.0 and 6.5. We are researching this problem and will post new information here in the Microsoft Knowledge Base as it becomes available.

Additional query words: index truncate delete insert row

Keywords          : kbnetwork SSrvTran_SQL kbbug6.50 kbbug6.00 
Version           : 6.0 6.5
Platform          : WINDOWS 
Issue type        : 

Last Reviewed: April 2, 1999