none
Trying to run sp_updatestats on each database from a stored procedure; Need some help

    Question

  • I am not sure whether I can do this in Stored procedure. Let me explain a bit

    We have several different databases in our SQL server and I like to run SP_UpdateStat on each of them at least once a week. To do this, first I stored the database details in a Table and then trying to call them one by one and run the SP_UpdateStat on them in a stored procedure. The problem is I cannot use 'USE' command in the stored procedure to run the sp_updatestat on each database (I created a cursor for the table and trying to read one by one in a loop)

    This is my code

    Alter PROCEDURE DbCacheCleaning 
    AS

    --Cursor declaring

    Declare CurDbName cursor
    For
    Select *
    From tblDbDetails

    --Opening the cursor

    Open CurDbName
     Declare @dbnames varchar(100),  @dbsizes Numeric, @remarks Varchar
     Fetch NEXT FROM CurDbName into @dbnames, @dbsizes, @remarks
     While (@@FETCH_STATUS<>-1)
     Begin
      use @dbnames -- This is where I am having problem
      exec sp_updatestats 
      Fetch NEXT FROM CurDbName into @dbnames, @dbsizes, @remarks 
     END

    Close CurDbName
    Deallocate CurDbName
    GO
     

    This SP is going to sit on general database and then trying to run for each database. Is this possible? Is there any other alternative method available (other than schduling job for each database and run the exec sp_updatestats? 


    Dreaming a world without any war in anywhere
    Tuesday, April 26, 2011 6:47 PM

Answers

  • Use dynamic SQL, for example

    Declare @SQL nvarchar(1000)
    While (@@FETCH_STATUS<>-1)
     Begin
     Set @SQL = 'Use ' + @dbnames + ' exec sp_updatestats'
     Exec (@SQL) 
     Fetch NEXT FROM CurDbName into @dbnames, @dbsizes, @remarks 
     END
    

    Tom

    • Marked as answer by Rajeeshun Wednesday, April 27, 2011 2:33 PM
    Tuesday, April 26, 2011 7:10 PM
  • Thanks Tom

     

    Before i posted this quested, I was not aware that I can do this (running SP_UpdateStats on all the databases) by setting up a single Maintenance task in the Server (That task run this Stored procedure on all or selected databases) I found that after I posted this and I setup the maintenance task to do this

    Thanks for your code help. I am marking both of our replies as answers in order to help others who face similar scenarios

     

     


    Dreaming a world without any war in anywhere
    • Marked as answer by Rajeeshun Wednesday, April 27, 2011 2:33 PM
    Wednesday, April 27, 2011 2:32 PM

All replies

  • Use dynamic SQL, for example

    Declare @SQL nvarchar(1000)
    While (@@FETCH_STATUS<>-1)
     Begin
     Set @SQL = 'Use ' + @dbnames + ' exec sp_updatestats'
     Exec (@SQL) 
     Fetch NEXT FROM CurDbName into @dbnames, @dbsizes, @remarks 
     END
    

    Tom

    • Marked as answer by Rajeeshun Wednesday, April 27, 2011 2:33 PM
    Tuesday, April 26, 2011 7:10 PM
  • Thanks Tom

     

    Before i posted this quested, I was not aware that I can do this (running SP_UpdateStats on all the databases) by setting up a single Maintenance task in the Server (That task run this Stored procedure on all or selected databases) I found that after I posted this and I setup the maintenance task to do this

    Thanks for your code help. I am marking both of our replies as answers in order to help others who face similar scenarios

     

     


    Dreaming a world without any war in anywhere
    • Marked as answer by Rajeeshun Wednesday, April 27, 2011 2:33 PM
    Wednesday, April 27, 2011 2:32 PM
  • http://www.sqlservercurry.com/2009/04/6-common-uses-of-undocumented-stored.html

    Its undocumented so use at your own risk!
    Balmukund Lakhani | Please mark solved if I've answered your question, vote for it as helpful to help other user's find a solution quicker
    --------------------------------------------------------------------------------
    This posting is provided "AS IS" with no warranties, and confers no rights.
    --------------------------------------------------------------------------------
    My Blog: http://blogs.msdn.com/blakhani
    Team Blog: http://blogs.msdn.com/sqlserverfaq
    Wednesday, April 27, 2011 2:50 PM