locked
Update statistics RRS feed

  • Question

  • Hi Team,<o:p></o:p>

                    we have upgraded sql server 2008 R2 to SQL server 2012 SP2. Now in order to Execute UPDATE STATISTICS in user-defined tables in SQL Server user databases,  can anyone share us a script as we have around 126 databases on this server.<o:p></o:p>

    <o:p> </o:p>

    Regards,

    Varun


    Thursday, September 10, 2015 6:52 AM

Answers

  • Varun,

    You can also use Ola hallengren index solution. It would do all index maintenance for your indexes.

    Read FAQ's before proceeding.


    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My Technet Wiki Article

    MVP

    • Proposed as answer by VidhyaSagar Thursday, September 10, 2015 10:21 AM
    • Marked as answer by Charlie Liao Tuesday, September 22, 2015 8:52 AM
    Thursday, September 10, 2015 8:38 AM
    Answerer

All replies

  • Hello Varun,

    Just run sp_updatestats (Transact-SQL) to update all statistics of all tables.


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Thursday, September 10, 2015 7:01 AM
    Answerer
  • DECLARE @tablename varchar(80),@shemaname varchar(80)
    DECLARE @SQL AS NVARCHAR(200)
    DECLARE TblName_cursor CURSOR FOR
    SELECT t.name,s.name FROM sys.tables t join sys.schemas s
    on s.schema_id=t.schema_id


    OPEN TblName_cursor

    FETCH NEXT FROM TblName_cursor
    INTO @tablename,@shemaname

    WHILE @@FETCH_STATUS = 0
    BEGIN
    SET @SQL = 'UPDATE STATISTICS '+@shemaname+'.[' + @TableName + '] WITH FULLSCAN ' ---+ CONVERT(varchar(3), @sample) + ' PERCENT'

    EXEC sp_executesql @statement = @SQL

       FETCH NEXT FROM TblName_cursor
       INTO @tablename,@shemaname
    END

    CLOSE TblName_cursor
    DEALLOCATE TblName_cursor

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Thursday, September 10, 2015 8:20 AM
  • Hello Varun,

    Just run sp_updatestats (Transact-SQL) to update all statistics of all tables.


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Hi,

     Does it updates stats of all objects on all user databases. reason why am asking is we have around 126 databases. please clarify.

    Regards,

    Varun

    Thursday, September 10, 2015 8:23 AM
  • Varun,

    You can also use Ola hallengren index solution. It would do all index maintenance for your indexes.

    Read FAQ's before proceeding.


    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My Technet Wiki Article

    MVP

    • Proposed as answer by VidhyaSagar Thursday, September 10, 2015 10:21 AM
    • Marked as answer by Charlie Liao Tuesday, September 22, 2015 8:52 AM
    Thursday, September 10, 2015 8:38 AM
    Answerer