Arthur Fuller
fuller.artful at gmail.com
Wed Sep 10 14:59:17 CDT 2008
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