Page 1 of 2 12 LastLast
Results 1 to 15 of 28

Thread: Nested Fetching

  1. #1
    Join Date
    Nov 2006
    Location
    Quebec
    Posts
    172

    Question Unanswered: Nested Fetching

    Hi all!

    I'm trying to write a T-SQL statement that will allow me to do a maintenance for all user table for all databases on my server.

    This is what I got so far :

    Code:
    DECLARE @cStatement varchar(255)
    DECLARE @dStatement varchar(255)
    
    DECLARE T_database CURSOR FOR SELECT '[' + CONVERT(varchar(64),name) + ']' FROM master.dbo.sysdatabases WHERE dbid>6
    
    SET nocount ON
    OPEN T_database
    FETCH NEXT FROM T_database INTO @dStatement 
    WHILE (@@FETCH_STATUS <> -1)
    begin
    	-- LOOP IMBRIQU&#201; POUR PASSER AU TRAVERS DE CHAQUES ELEMENTS
    	-- DE LA BASE DE DONN&#201;ES ACTUELLE.
    	EXEC ('DECLARE T_cursor CURSOR FOR SELECT ''UPDATE STATISTICS ['' +  CONVERT(varchar(64),name) + '']'' FROM ' + @dStatement + '.dbo.sysobjects WHERE type = ''U''')
    	OPEN T_cursor
    	FETCH NEXT FROM T_curosr INTO @cStatement
    	WHILE (@@FETCH_STATUS <> -1)
    	begin
    		PRINT(@cStatement)
    		FETCH NEXT FROM T_cursor INTO @cStatement
    	end
    	DEALLOCATE T_cursor
    	FETCH NEXT FROM T_database INTO @dStatement 
    end
    
    DEALLOCATE T_database
    But I get a sh**t load of errors :

    Code:
    Msg 16916, Level 16, State 1, Line 15
    A cursor with the name 'T_curosr' does not exist.
    Msg 16916, Level 16, State 1, Line 15
    A cursor with the name 'T_curosr' does not exist.
    Msg 16916, Level 16, State 1, Line 15
    A cursor with the name 'T_curosr' does not exist.
    Msg 16916, Level 16, State 1, Line 15
    A cursor with the name 'T_curosr' does not exist.
    Msg 16916, Level 16, State 1, Line 15
    A cursor with the name 'T_curosr' does not exist.
    Msg 16916, Level 16, State 1, Line 15
    A cursor with the name 'T_curosr' does not exist.
    Msg 16916, Level 16, State 1, Line 15
    A cursor with the name 'T_curosr' does not exist.
    Msg 16916, Level 16, State 1, Line 15
    A cursor with the name 'T_curosr' does not exist.
    Please help!!!

    Last edited by ortho; 05-21-08 at 14:46.
    Less is more.
    How long is now?
    http://www.lesouterrain.com

  2. #2
    Join Date
    Nov 2006
    Location
    Quebec
    Posts
    172
    Sorry guys,

    My error was typo, again, loll...

    So here it is, maybe it can help someone else:

    Code:
    DECLARE @cStatement varchar(255)
    DECLARE @dStatement varchar(255)
    
    DECLARE T_database CURSOR FOR SELECT '[' + CONVERT(varchar(64),name) + ']' FROM master.dbo.sysdatabases WHERE dbid>6
    
    SET nocount ON
    OPEN T_database
    FETCH NEXT FROM T_database INTO @dStatement 
    WHILE (@@FETCH_STATUS <> -1)
    begin
    	-- LOOP IMBRIQUÉ POUR PASSER AU TRAVERS DE CHAQUES ELEMENTS
    	-- DE LA BASE DE DONNÉES ACTUELLE.
    	EXEC ('DECLARE T_cursor CURSOR FOR SELECT ''UPDATE STATISTICS ' + @dStatement + '.dbo.['' +  CONVERT(varchar(64),name) + '']'' FROM ' + @dStatement + '.dbo.sysobjects WHERE type = ''U''')
    	OPEN T_cursor
    	FETCH NEXT FROM T_cursor INTO @cStatement
    	WHILE (@@FETCH_STATUS <> -1)
    	begin
    		PRINT(@cStatement)
    		FETCH NEXT FROM T_cursor INTO @cStatement
    	end
    	DEALLOCATE T_cursor
    	FETCH NEXT FROM T_database INTO @dStatement 
    end
    
    DEALLOCATE T_database
    Less is more.
    How long is now?
    http://www.lesouterrain.com

  3. #3
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Isn't this simpler?

    use <database_name>
    go
    exec sp_updatestats
    go
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  4. #4
    Join Date
    Nov 2006
    Location
    Quebec
    Posts
    172
    for the first part no... cause if I create a new db I don't want to have to modify my sp. but for the second part you're right it's more simple to apply it to the db than applying it to every single objects.


    Thanks

    O.
    Less is more.
    How long is now?
    http://www.lesouterrain.com

  5. #5
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    guess you need to address your methodology
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  6. #6
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Quote Originally Posted by ortho
    for the first part no... cause if I create a new db I don't want to have to modify my sp. but for the second part you're right it's more simple to apply it to the db than applying it to every single objects.


    Thanks

    O.
    Hmmm...I thought it was obvious, but...Try this:
    exec sp_msforeachdb 'if db_id(''?'') != db_id(''master'') and db_id(''?'') != db_id(''tempdb'') begin use ?;exec sp_updatestats;end'
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  7. #7
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Be fair, Robert! "sp_msforeachdb" isn't even a documented procedure. I wouldn't expect most people to know about it.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  8. #8
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    While I wholeheartedly approve of rdjabarov's solution, and it is what I'd recommend, the solution neatly sidesteps the original question which is how do you do nested cursors that rely upon dynamic execution.

    Before I digress into this discusssion, let me make it perfectly clear that the following code pushes the naughty meter clear off of the scale. This is not recommended as a good idea, as a good coding practice, or a way to have a good day. Do this only in private, and wash your hands after you are done!

    The problem comes from the way that dynamic execution works... Dynamic code is executed in a separate SQL "context" from the spid that executed the code. You can SET options, switch databases, and create temp tables inside of dynamic code, but those changes won't be "visible" to the procedure that executed the dynamic code... They exist only within the context of the dynamically executed code and its descendants.

    The descendants part of that statement is why you can monkey up the world in a stored procedure (like sp_MSforeachdb) and have those changes used by a called procedure (like sp_updatestats). This is the "correct" way to handle issues like this.

    In order to handle things "inline" in a script, you have to be "smarter than the average bear", or at least willing to do something a bit unnatural to achieve your goals. The trick is to do everything in one context, completely dynamically so that you don't lose your context at the wrong moment. The following code is proof positive that given enough thrust, you can make anything fly:
    Code:
    --  ptp  20080521  See http://www.dbforums.com/showthread.php?t=1630550
    
    DECLARE @cDb		sysname
    
    DECLARE zDb CURSOR FOR SELECT QuoteName(name)
       FROM master.dbo.sysdatabases
       WHERE name NOT IN ('master', 'tempdb')
    
    OPEN zDb
    FETCH zDb INTO @cDb
    
    WHILE 0 = @@fetch_status
       BEGIN
          EXECUTE ('USE ' + @cDb
    +  '     DECLARE @cTable sysname '
    +  '     DECLARE zTable CURSOR FOR SELECT QuoteName(name) '
    +  '        FROM dbo.sysobjects '
    +  '        WHERE  ''U'' = type '
    
    +  '     OPEN zTable '
    +  '     FETCH zTable INTO @cTable '
    
    +  '     WHILE 0 = @@fetch_status '
    +  '        BEGIN '
    +  '           EXECUTE (''UPDATE STATISTICS '' + @cTable) '
    +  '           FETCH zTable INTO @cTable '
    +  '         END '
    
    +  '      CLOSE zTable '
    +  '      DEALLOCATE zTable ')
    
          FETCH zDb INTO @cDb
       END
    
    CLOSE zDb
    DEALLOCATE zDb
    Now, after you read this go ahead and think the steps through... After you go Ewwwwwwwwwwww you can go wash your hands, then forget that you ever thought about this kind of thing!

    -PatP

  9. #9
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Pat Phelan, Phorum Post Porn Purveyor.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  10. #10
    Join Date
    Nov 2006
    Location
    Quebec
    Posts
    172
    Quote Originally Posted by Pat Phelan
    While I wholeheartedly approve of rdjabarov's solution, and it is what I'd recommend, the solution neatly sidesteps the original question which is how do you do nested cursors that rely upon dynamic execution.

    Before I digress into this discusssion, let me make it perfectly clear that the following code pushes the naughty meter clear off of the scale. This is not recommended as a good idea, as a good coding practice, or a way to have a good day. Do this only in private, and wash your hands after you are done!

    The problem comes from the way that dynamic execution works... Dynamic code is executed in a separate SQL "context" from the spid that executed the code. You can SET options, switch databases, and create temp tables inside of dynamic code, but those changes won't be "visible" to the procedure that executed the dynamic code... They exist only within the context of the dynamically executed code and its descendants.

    The descendants part of that statement is why you can monkey up the world in a stored procedure (like sp_MSforeachdb) and have those changes used by a called procedure (like sp_updatestats). This is the "correct" way to handle issues like this.

    In order to handle things "inline" in a script, you have to be "smarter than the average bear", or at least willing to do something a bit unnatural to achieve your goals. The trick is to do everything in one context, completely dynamically so that you don't lose your context at the wrong moment. The following code is proof positive that given enough thrust, you can make anything fly:
    Code:
    --  ptp  20080521  See http://www.dbforums.com/showthread.php?t=1630550
    
    DECLARE @cDb		sysname
    
    DECLARE zDb CURSOR FOR SELECT QuoteName(name)
       FROM master.dbo.sysdatabases
       WHERE name NOT IN ('master', 'tempdb')
    
    OPEN zDb
    FETCH zDb INTO @cDb
    
    WHILE 0 = @@fetch_status
       BEGIN
          EXECUTE ('USE ' + @cDb
    +  '     DECLARE @cTable sysname '
    +  '     DECLARE zTable CURSOR FOR SELECT QuoteName(name) '
    +  '        FROM dbo.sysobjects '
    +  '        WHERE  ''U'' = type '
    
    +  '     OPEN zTable '
    +  '     FETCH zTable INTO @cTable '
    
    +  '     WHILE 0 = @@fetch_status '
    +  '        BEGIN '
    +  '           EXECUTE (''UPDATE STATISTICS '' + @cTable) '
    +  '           FETCH zTable INTO @cTable '
    +  '         END '
    
    +  '      CLOSE zTable '
    +  '      DEALLOCATE zTable ')
    
          FETCH zDb INTO @cDb
       END
    
    CLOSE zDb
    DEALLOCATE zDb
    Now, after you read this go ahead and think the steps through... After you go Ewwwwwwwwwwww you can go wash your hands, then forget that you ever thought about this kind of thing!

    -PatP
    Brillant!
    Less is more.
    How long is now?
    http://www.lesouterrain.com

  11. #11
    Join Date
    Nov 2006
    Location
    Quebec
    Posts
    172
    Quote Originally Posted by rdjabarov
    Hmmm...I thought it was obvious, but...Try this:
    exec sp_msforeachdb 'if db_id(''?'') != db_id(''master'') and db_id(''?'') != db_id(''tempdb'') begin use ?;exec sp_updatestats;end'
    Obvious, not at all... I never heard of it before.

    But just one thing can you explain me the use of those questionmarks please ?
    Less is more.
    How long is now?
    http://www.lesouterrain.com

  12. #12
    Join Date
    Apr 2007
    Posts
    183

    Too late...

    Code:
    SET NOCOUNT ON
    
    CREATE TABLE	#db
    		(
    			dbID INT,
    			dbName SYSNAME
    		)
    
    INSERT	#db
    	(
    		dbID,
    		dbName
    	)
    SELECT	dbid,
    	name
    FROM	master..sysdatabases
    WHERE	name NOT IN ('tempdb', 'master', 'model', 'msdb')
    
    CREATE TABLE	#tbl
    		(
    			tblID INT IDENTITY(1, 1),
    			tblName SYSNAME
    		)
    
    DECLARE	@ID INT,
    	@SQL VARCHAR(8000)
    
    SELECT	@ID = MIN(dbID)
    FROM	#db
    
    WHILE @ID IS NOT NULL
    	BEGIN
    		SELECT	@SQL =	'
    					INSERT	#tbl
    						(
    							tblName
    						)
    					SELECT	QUOTENAME(TABLE_CATALOG) + ''.'' + QUOTENAME(TABLE_SCHEMA) + ''.'' + QUOTENAME(TABLE_NAME)
    					FROM	' + QUOTENAME(dbName) + '.INFORMATION_SCHEMA.TABLES
    					WHERE	TABLE_TYPE = ''BASE TABLE''
    				'
    		FROM	#db
    		WHERE	dbID = @ID
    
    		--PRINT	@SQL
    		EXEC	(@SQL)
    
    		SELECT	@ID = MIN(dbID)
    		FROM	#db
    		WHERE	dbID > @ID
    	END
    
    DROP TABLE	#db
    
    SET	@ID = IDENT_CURRENT('#tbl')
    
    WHILE @ID > 0
    	BEGIN
    		SELECT	@SQL = 'UPDATE STATISTICS ' + tblName + ' WITH FULLSCAN, ALL',
    			@ID = @ID - 1
    		FROM	#tbl
    		WHERE	tblID = @ID
    
    		--PRINT @SQL
    		EXEC	(@SQL)
    	END
    
    DROP TABLE	#tbl

  13. #13
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Quote Originally Posted by ortho
    But just one thing can you explain me the use of those questionmarks please ?
    Code:
    EXECUTE sp_MSforeachdb 'print ''?'''
    EXECUTE sp_MSforeachtable 'print ''?'''
    Edit: I decided that I should expand on this example a bit...

    The whole sp_MSforeach% family of stored procedures are "iterators" that cycle through a list of something, executing code passed as a parameter once for each something involved. On each interation, the iterator substitutes the name of the latest something for every "substitution character" (a question mark by default) in the command string. Another way to write the same process, although a bit more cryptic would be:
    Code:
    EXECUTE sp_MSforeachdb 'EXECUTE sp_MSforeachtable ''UPDATE STATISTICS !'', ''!''', '?'
    or, if you were in an exceptionally nasty mood:
    Code:
    EXECUTE sp_MSforeachdb 'EXECUTE sp_MSforeachtable "UPDATE STATISTICS ?"', '!'
    -PatP
    Last edited by Pat Phelan; 05-22-08 at 12:36.

  14. #14
    Join Date
    Nov 2006
    Location
    Quebec
    Posts
    172
    Quote Originally Posted by Pat Phelan
    Code:
    EXECUTE sp_MSforeachdb 'print ''?'''
    EXECUTE sp_MSforeachtable 'print ''?'''
    -PatP
    Oh I see... is it documented somewhere?

    Thank you btw.

    Or-Tho!


    ---------- Edited --------------

    Thank you Pat! Once again you are the light bulb over the top of my head!
    Last edited by ortho; 05-22-08 at 12:37.
    Less is more.
    How long is now?
    http://www.lesouterrain.com

  15. #15
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by blindman
    Be fair, Robert! "sp_msforeachdb" isn't even a documented procedure. I wouldn't expect most people to know about it.
    He he!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •