I had a problem with a maintenance procedure and need to create a second one where I declare a cursor with a list of database ID and pass them into another cursor to get a list of tables for each database.
Current problem is that in the inner cursor even though it runs use [database_name], when i declare it and specify my query it selects the tables from the master database. it doesn’t change the database context before going into the inner cursor.
DECLARE @db varchar(128) declare @cmd varchar(1024) declare @table varchar(255) declare @cmd2 varchar(1024) DECLARE crDB CURSOR global FORWARD_only FOR SELECT [name] FROM sys.databases WHERE database_id > 4 and database_id in (33) ORDER BY [name] OPEN crDB FETCH crDB INTO @db WHILE @@FETCH_STATUS = 0 BEGIN SET @cmd = 'USE [' + @db +']' EXEC (@cmd) DECLARE crTB CURSOR LOCAL FAST_FORWARD FOR select [name] from sys.objects where type = 'u' ORDER BY [name] OPEN crTB FETCH crTB INTO @table WHILE @@FETCH_STATUS = 0 BEGIN SET @cmd2 = 'Update Statistics ' + @table + CHAR(13) PRINT @cmd2 EXEC (@cmd2) end CLOSE crTB DEALLOCATE crTB FETCH crDB INTO @db END CLOSE crDB DEALLOCATE crDB
GO
Advertisement
Answer
The issue with your inner cursor, is scope. You can to do 2 things here. You have to move your inner cursor to right after the USE [' + @db
like:
DECLARE @db VARCHAR(128); DECLARE @cmd VARCHAR(1024); DECLARE @table VARCHAR(255); DECLARE @cmd2 VARCHAR(1024); DECLARE crDB CURSOR GLOBAL READ_ONLY FORWARD_ONLY FOR SELECT name FROM sys.databases WHERE database_id > 4 AND database_id IN (33) ORDER BY name; OPEN crDB; FETCH crDB INTO @db; WHILE @@FETCH_STATUS = 0 BEGIN SET @cmd = 'USE [' + @db + '] GO; DECLARE crTB CURSOR LOCAL FAST_FORWARD FOR SELECT name FROM sys.objects WHERE type = ''u''; ORDER BY name; OPEN crTB; FETCH NEXT FROM crTB INTO @table; WHILE @@FETCH_STATUS = 0 BEGIN SET @cmd2 = ''Update Statistics '' + @table + CHAR(13); PRINT @cmd2; EXEC (@cmd2); END; CLOSE crTB; DEALLOCATE crTB; '; EXEC (@cmd); FETCH NEXT FROM crDB INTO @db; END; CLOSE crDB; DEALLOCATE crDB;
Or you can get rid of the inner cursor altogether and use sys.sp_MSforeachtable:
WHILE @@FETCH_STATUS = 0 BEGIN SET @cmd = 'USE [' + @db + '] GO; EXEC sys.sp_MSforeachtable @command1 = ''UPDATE STATISTICS ?;'''; EXEC (@cmd);