[dba-SQLServer] Very strange behavior

In the simple case of Northwind you are correct, but try it against
Adventureworks and you will see the problem. Try running these statements:


EXEC
sp_MSForeachtable ('ALTER INDEX ALL ON ' + schema_name() + '.' ? + 'REBUILD
WITH ( PAD_INDEX = OFF, SORT_IN_TEMPDB = ON, STATISTICS_NORECOMPUTE = ON )')

EXEC
sp_MSForeachtable ('ALTER INDEX ALL ON ? REBUILD WITH ( PAD_INDEX = OFF,
SORT_IN_TEMPDB = ON, STATISTICS_NORECOMPUTE = ON )')
The apparent failure of sp_MSForEachTable to handle these variations on the
statement is what motivated me to look beyond sp_MSForEachTable.

A.

On Wed, Sep 10, 2008 at 5:42 PM, James Barash <James at fcidms.com> wrote:

> Arthur:
>
> I think the problem is with the CHAR(39) that you wrap the Update in. I
> tried running it without those and it run file except for tables that have
> a
> space in the tablename. I modified it to:
>
> DECLARE Statement_Cursor CURSOR
>    FOR SELECT
>  'UPDATE STATISTICS [' + SCHEMA_NAME() + '].[' + sysobjects.NAME + ']'
>
>        FROM
>            sysobjects
>        WHERE
>            TYPE = 'U'
>
> However, there is an easier way to accomplish this:
>
> EXEC sp_MSForeachtable 'UPDATE STATISTICS ?'
>
>