Update Stats with full scan on all tables in all databases

  • I run the following code which worked sql2k but does not work in sql2k5 and get the following error. I tried with and without semicolon, chaning quotations. Any help appreciated!

    --Update Statistics on all tables in all DBs

    Set Nocount on

    Declare db Cursor For

    Select name from master.dbo.sysdatabases where dbid>=5 --Doesnt include system dbs

    Declare @dbname varchar(60)

    Declare @execmd nvarchar(150)

    Open db

    Fetch Next from db into @dbname

    While @@Fetch_status=0

    begin

    if @dbname is null

    Begin

    Print 'null Value'

    end

    else

    Begin

    PRINT '###########################################################################'

    PRINT 'Update Statistics in ' + @dbname

    SELECT @execmd = 'USE ' + @dbname + ''Exec sp_MSForEachTable 'Update Statistics ''?'' with FULLSCAN'''

    EXEC(@execmd)

    PRINT ''

    End

    Fetch Next from db into @dbname

    end

    Close db

    Deallocate db

    GO

    Error:

    Msg 102, Level 15, State 1, Line 1

    Incorrect syntax near '[Production].[ProductProductPhoto]'.

    Msg 319, Level 15, State 1, Line 1

    Incorrect syntax near the keyword 'with'. If this statement is a common table expression or an xmlnamespaces clause, the previous statement must be terminated with a semicolon.

    Msg 105, Level 15, State 1, Line 1

    Unclosed quotation mark after the character string ''.

    ¤ §unshine ¤

  • sunshine (9/15/2008)


    the previous statement must be terminated with a semicolon.

    this message is fairly descriptive!! The USE statement and exec must be separated by a semi colon like so

    SELECT @execmd = 'USE ' + @dbname + '';Exec sp_MSForEachTable 'Update Statistics ''?'' with FULLSCAN'''

    -----------------------------------------------------------------------------------------------------------

    "Ya can't make an omelette without breaking just a few eggs" 😉

  • Perry Whittle (9/16/2008)


    sunshine (9/15/2008)


    the previous statement must be terminated with a semicolon.

    this message is fairly descriptive!! The USE statement and exec must be separated by a semi colon like so

    That message is just saying that if the with is starting a CTE (which in this case it is not) the previous command needs to be terminated with a ;

    It's a generic message that is given any time there's a syntax error and the keywork 'with' is anywhere nearby. In this case, it's misleading as the problem has nothing to do with the 'with'

    In this case, the problem is with the single quotes. Change the select to the following and it works (in my 2008 test instance)

    SELECT @execmd = 'USE ' + @dbname + ' Exec sp_MSForEachTable ''Update Statistics ? with FULLSCAN'''

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • I had originally tried the semi-colon and it did not work. Gail, your fix helped. Thank you so much for your help as always!

    ¤ §unshine ¤

  • I am having job running SSIS package,which is having 4 steps as below

    1.Database Integrity -- success

    2.Clean Up History --success

    3.Update Statistics --- failed

    4.Reorganize Index--success

    -->update statistics got failed due to below error..

    Failed:(-1073548784) Executing the query "UPDATE STATISTICS [dbo].[PDE_RFEQP_MVMNT_EVENT_DN] WITH FULLSCAN " failed with the following error: "A severe error occurred on the current command. The results, if any, should be discarded. A severe error occurred on the current command. The results, if any, should be discarded.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

    Can any one help me pls...

  • How could you use the following code using sample percent using variable to pass percentage?

  • See what message you receive when you try to run it under management studio...

    MJ

  • sp_msforeachtable 'update statistics ? with all'

    it will work fine

  • whenever I use sp_msforeach... I always SET QUOTED_IDENTIFIER OFF first. Then I can use DOUBLE quotes for the outer (executed) strings and then single quotes for inner string creation. Works like a champ, and I never have to worry with is it 1, 2, 3, 4, etc single quotes I have to put together to get the desired effect!! 😎

    Best,
    Kevin G. Boles
    SQL Server Consultant
    SQL MVP 2007-2012
    TheSQLGuru on googles mail service

  • Set Nocount on

    Declare db Cursor For

    Select name from master.dbo.sysdatabases where dbid>=11 --Doesnt include system dbs

    Declare @dbname varchar(60)

    Declare @execmd nvarchar(150)

    Open db

    Fetch Next from db into @dbname

    While @@Fetch_status=0

    begin

    if @dbname is null

    Begin

    Print 'null Value'

    end

    else

    Begin

    PRINT '###########################################################################'

    PRINT 'Update Statistics in ' + @dbname

    SELECT @execmd = 'USE ' + @dbname + ' Exec sp_msforeachtable ''Update Statistics ? with FULLSCAN'''

    EXEC(@execmd)

    PRINT ''

    End

    Fetch Next from db into @dbname

    end

    Close db

    Deallocate db

    GO

    All,

    i tried the above code and made a moification where the number of database being pulled for testing purpose is 2

    When i run it, i get the following error message:

    ###########################################################################

    Update Statistics in xxx91B

    Msg 2812, Level 16, State 62, Line 1

    Could not find stored procedure 'sp_msforeachtable'.

    ###########################################################################

    Update Statistics in xxx91C

    Msg 2812, Level 16, State 62, Line 1

    Could not find stored procedure 'sp_msforeachtable'.

    When i just run the following:

    it runs just fine

    use xxx91C

    exec sp_MSforeachtable 'update statistics ? with fullscan'

    go

    Any help would be appreciated.(SQL 08R2 RTM)

    Noli Timere
  • You can the following query instead of the one mentioned. I see your query is consuming more cpu which is not good for prod.

    Hope you can use my query.

    SET NOCOUNT ON

    DECLARE @SQLcommand NVARCHAR(512),

    @Table SYSNAME

    DECLARE curAllTables CURSOR FOR

    SELECT table_schema + '.' + table_name

    FROM information_schema.tables

    WHERE TABLE_TYPE = 'BASE TABLE'

    OPEN curAllTables

    FETCH NEXT FROM curAllTables

    INTO @Table

    WHILE (@@FETCH_STATUS = 0)

    BEGIN

    PRINT N'UPDATING STATISTICS FOR TABLE: ' + @Table

    SET @SQLcommand = 'UPDATE STATISTICS ' + @Table + ' WITH FULLSCAN'

    EXEC sp_executesql

    @SQLcommand

    FETCH NEXT FROM curAllTables

    INTO @Table

    END

    CLOSE curAllTables

    DEALLOCATE curAllTables

    SET NOCOUNT OFF

  • Or this:

    eclare @SQLCmd varchar(max);

    select

    @SQLCmd = (select 'UPDATE STATISTICS ' + object_name(object_id) + ' with fullscan;' + char(13) + char(10)

    from

    sys.tables

    for xml path (''),type).value('.','varchar(max)');

    print @SQLCmd;

    exec (@SQLCmd);

  • My two cents. If you are looking for something to run in parallel across all databases, you could either use Red Gate multiscript tool ( worth every penny for this one !!!) or use this [/url] which will need little manual work but this also works great.

  • Ola.hallengren.com

    Best maintenance stuff out there IMNSHO!! 🙂

    Best,
    Kevin G. Boles
    SQL Server Consultant
    SQL MVP 2007-2012
    TheSQLGuru on googles mail service

Viewing 14 posts - 1 through 13 (of 13 total)

You must be logged in to reply to this topic. Login to reply