use UPDATE STATISTICS @table in a cursor

27 Jan 2006 2:20 AM

SeanH

The script below is a cursor. I get the following error when I run it:
--{Server: Msg 170, Level 15, State 1, Line 14
Line 14: Incorrect syntax near '@table'.}--
The error is caused by this phrase: -- UPDATE STATISTICS @table -- How do I
resolve this.

***********************************************************
SET NOCOUNT ON
USE db_control
DECLARE @table varchar(10)
DECLARE EDI_cursor CURSOR FOR
SELECT table_name FROM INFORMATION_SCHEMA.TABLES
WHERE table_type ='Base Table'
ORDER BY table_name
OPEN EDI_cursor
FETCH NEXT FROM EDI_cursor
INTO @table
WHILE @@FETCH_STATUS = 0
BEGIN
select @table
UPDATE STATISTICS @table
FETCH NEXT FROM EDI_cursor
INTO @table
END
CLOSE EDI_cursor
DEALLOCATE EDI_cursor
GO
**********************************************************

27 Jan 2006 3:22 AM

Andrew J. Kelly

Obviously that command does not take a variable for that parameter. You can
use dynamic sql but I question why you are doing this at all. You can use
the built in stored procedure sp_updatestats to update the stats on all
tables. There is no need to use a cursor.

--
Andrew J. Kelly SQL MVP

Show quote

"SeanH" <s***@persiandragon.com> wrote in message
news:ucsFIhuIGHA.208@tk2msftngp13.phx.gbl...
> The script below is a cursor. I get the following error when I run it:
> --{Server: Msg 170, Level 15, State 1, Line 14
> Line 14: Incorrect syntax near '@table'.}--
> The error is caused by this phrase: -- UPDATE STATISTICS @table -- How do
> I
> resolve this.
>
> ***********************************************************
> SET NOCOUNT ON
> USE db_control
> DECLARE @table varchar(10)
> DECLARE EDI_cursor CURSOR FOR
> SELECT table_name FROM INFORMATION_SCHEMA.TABLES
> WHERE table_type ='Base Table'
> ORDER BY table_name
> OPEN EDI_cursor
> FETCH NEXT FROM EDI_cursor
> INTO @table
> WHILE @@FETCH_STATUS = 0
> BEGIN
> select @table
> UPDATE STATISTICS @table
> FETCH NEXT FROM EDI_cursor
> INTO @table
> END
> CLOSE EDI_cursor
> DEALLOCATE EDI_cursor
> GO
> **********************************************************
>
>

28 Jan 2006 2:34 AM

SeanH

Thank you, I will use your suggestion.

Show quote

"Andrew J. Kelly" <sqlmvpnooospam@shadhawk.com> wrote in message
news:OUHPwDvIGHA.3984@TK2MSFTNGP14.phx.gbl...
> Obviously that command does not take a variable for that parameter. You
can
> use dynamic sql but I question why you are doing this at all. You can use
> the built in stored procedure sp_updatestats to update the stats on all
> tables. There is no need to use a cursor.
>
> --
> Andrew J. Kelly SQL MVP
>
>
> "SeanH" <s***@persiandragon.com> wrote in message
> news:ucsFIhuIGHA.208@tk2msftngp13.phx.gbl...
> > The script below is a cursor. I get the following error when I run it:
> > --{Server: Msg 170, Level 15, State 1, Line 14
> > Line 14: Incorrect syntax near '@table'.}--
> > The error is caused by this phrase: -- UPDATE STATISTICS @table -- How
do
> > I
> > resolve this.
> >
> > ***********************************************************
> > SET NOCOUNT ON
> > USE db_control
> > DECLARE @table varchar(10)
> > DECLARE EDI_cursor CURSOR FOR
> > SELECT table_name FROM INFORMATION_SCHEMA.TABLES
> > WHERE table_type ='Base Table'
> > ORDER BY table_name
> > OPEN EDI_cursor
> > FETCH NEXT FROM EDI_cursor
> > INTO @table
> > WHILE @@FETCH_STATUS = 0
> > BEGIN
> > select @table
> > UPDATE STATISTICS @table
> > FETCH NEXT FROM EDI_cursor
> > INTO @table
> > END
> > CLOSE EDI_cursor
> > DEALLOCATE EDI_cursor
> > GO
> > **********************************************************
> >
> >
>
>