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