[dba-SQLServer] Very strange behavior

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 ?'

This uses an undocumented stored procedure in the Master database. It works
in SQL Server 2000/2005 at least. I don't know about other versions.

Hope this helps.

James Barash



-----Original Message-----
From: dba-sqlserver-bounces at databaseadvisors.com
[mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of Arthur
Fuller
Sent: Wednesday, September 10, 2008 3:59 PM
To: Discussion concerning MS SQL Server
Subject: [dba-SQLServer] Very strange behavior

The following code declares a cursor and then generates one SQL
statement per table in the current database, placing the statement into the
cursor. We then walk the cursor and either print or execute each statement.
Run the code as is against any database. In this example I'm using
Northwind:

<sql>
-- Build Cursor of UPDATE STATISTICS Statements
DECLARE Statement_Cursor CURSOR
    FOR SELECT
  CHAR(39) + 'UPDATE STATISTICS ' + SCHEMA_NAME() + '.' + sysobjects.NAME +
CHAR(39)
        FROM
            sysobjects
        WHERE
            TYPE = 'U'
OPEN Statement_Cursor
FETCH NEXT FROM Statement_Cursor INTO @statement

WHILE @@FETCH_STATUS = 0
    BEGIN
--  SET @statement = CHAR(39)+ at statement+CHAR(39)
--  This is the test statement to ensure that it works
       print @statement
--  This is the production statement that will take a long time
--  EXEC (@statement)
        FETCH NEXT FROM Statement_Cursor INTO @statement
    END
CLOSE Statement_Cursor
DEALLOCATE Statement_Cursor
-- example:
/*
USE NorthwindCS
DECLARE @sql VARCHAR(255)
SET @sql = 'UPDATE STATISTICS dbo.Employees'
--print @sql
EXEC (@sql)
*/
</sql>

To repeat my experiments, do this:

1. Run the code as is. It will print out lovely SQL statements.

2. Uncomment the EXEC(@statement) line and run the code again. You will get
a bunch of errors.

3. Select the code within the example and run it. The code works and you get
a nice message.

The string being passed to the EXEC in the example is identical to those
produced in the main code. Why it works in one case and not the
other baffles me.

Got any ideas?

I think this can be a cool and useful chunk of code once I figure a way out
of this problem. I have had my colleague Matthew look at it and he too is
baffled. So we need a third and maybe fourth and maybe fifth set of eyes.

TIA,

Arthur
_______________________________________________
dba-SQLServer mailing list
dba-SQLServer at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
http://www.databaseadvisors.com