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

Last reviewed: April 9, 1997
Article ID: Q156025

The information in this article applies to:
  • Microsoft SQL Server, versions 6.0 and 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 : kbbug6.00 kbbug6.50 kbnetwork SSrvTran_SQL
Version : 6.0 6.5
Platform : WINDOWS
Issue type : kbdocerr


THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY.

Last reviewed: April 9, 1997
© 1998 Microsoft Corporation. All rights reserved. Terms of Use.