The script below has three distinct advantages over the traditional method of just running sp_updatestats against the entire database:

1. It updates the stats starting with the smallest table first and finishing with the largest table. This provides the most bang for your buck when time is of the essence.
2. The messages tab is constantly being updated with the progress of the script (including a counter showing how many tables remain). This can be helpful when trying to determine if the script is close to done and should be allowed to keep running or if it should be cancelled to free up resources.
3. Provides the option to update statistics WITH FULLSCAN instead of just based on a sample of the data.

When you are faced with a serious performance issue one of the lowest risk solutions is to update statistics on your tables (even if you’ve set your DB to automatically update stats).  Unfortunately, for various reasons statistics on your tables can be come inaccurate to the point that a simple “UPDATE STATISTICS” does not resolve the issue.  When faced with an urgent performance issue updating statistics with the “WITH FULLSCAN” option can be exactly what SQL Server needs to find the optimal execution plan.  The bad news is the system stored procedure sp_updatestats does not provide the ability to update your statistics with the FULLSCAN option. Execute the query below to update statistics with full scan on all tables in your database.   Check the SSMS messages window to see the progress of the proc while it is running. It will display realtime messages similar to the following:

Table 1 of 84: Running 'UPDATE STATISTICS [dbo].[ErrorLog]' (0 rows)
Table 2 of 84: Running 'UPDATE STATISTICS [dbo].[kproduct_xml_doc]' (0 rows)
Table 3 of 84: Running 'UPDATE STATISTICS [dbo].[test3]' (0 rows)
Table 4 of 84: Running 'UPDATE STATISTICS [dbo].[ProspectiveCustomers]' (0 rows)
Table 5 of 84: Running 'UPDATE STATISTICS [dbo].[AWBuildVersion]' (1 rows)
...
Table 80 of 84: Running 'UPDATE STATISTICS [Production].[WorkOrderRouting]' (67131 rows)
Table 81 of 84: Running 'UPDATE STATISTICS [Production].[WorkOrder]' (72591 rows)
Table 82 of 84: Running 'UPDATE STATISTICS [Production].[TransactionHistoryArchive]' (89253 rows)
Table 83 of 84: Running 'UPDATE STATISTICS [Production].[TransactionHistory]' (113443 rows)
Table 84 of 84: Running 'UPDATE STATISTICS [Sales].[SalesOrderDetail]' (121317 rows)

Look good? The script below is what you’re looking for. By default it runs with FULLSCAN off. Set @WithFullscan = 1 to have every table run with FULLSCAN on.

    SET NOCOUNT ON
    GO

    --Determine if you want to execute the script with FULLSCAN
    DECLARE @WithFullscan BIT
    SELECT  @WithFullscan = 0

    -------------------
    --Begin script
    -------------------

    DECLARE @StartTime DATETIME

    SELECT  @StartTime = GETDATE()

    IF OBJECT_ID('tempdb..#TablesToUpdateStats') IS NOT NULL 
        BEGIN
            DROP TABLE #TablesToUpdateStats
        END

    DECLARE @NumTables VARCHAR(20)

    SELECT  s.[Name] AS SchemaName
          , t.[name] AS TableName
          , SUM(p.rows) AS RowsInTable
    INTO    #TablesToUpdateStats
    FROM    sys.schemas s
            LEFT JOIN sys.tables t
                ON s.schema_id = t.schema_id
            LEFT JOIN sys.partitions p
                ON t.object_id = p.object_id
            LEFT JOIN sys.allocation_units a
                ON p.partition_id = a.container_id
    WHERE   p.index_id IN ( 0, 1 ) -- 0 heap table , 1 table with clustered index
            AND p.rows IS NOT NULL
            AND a.type = 1  -- row-data only , not LOB
    GROUP BY s.[Name]
          , t.[name]
    SELECT  @NumTables = @@ROWCOUNT

    DECLARE updatestats CURSOR
    FOR
        SELECT  ROW_NUMBER() OVER ( ORDER BY ttus.RowsInTable )
              , ttus.SchemaName
              , ttus.TableName
              , ttus.RowsInTable
        FROM    #TablesToUpdateStats AS ttus
        ORDER BY ttus.RowsInTable
    OPEN updatestats

    DECLARE @TableNumber VARCHAR(20)
    DECLARE @SchemaName NVARCHAR(128)
    DECLARE @tableName NVARCHAR(128)
    DECLARE @RowsInTable VARCHAR(20)
    DECLARE @Statement NVARCHAR(300)
    DECLARE @Status NVARCHAR(300)
    DECLARE @FullScanSQL VARCHAR(20)

    IF @WithFullscan = 1 
        BEGIN
            SELECT  @FullScanSQL = ' WITH FULLSCAN'
        END
    ELSE 
        BEGIN --If @WithFullscan<>1 then set @FullScanSQL to empty string
            SELECT  @FullScanSQL = ''
        END

    FETCH NEXT FROM updatestats INTO @TableNumber, @SchemaName, @tablename,
        @RowsInTable
    WHILE ( @@FETCH_STATUS = 0 ) 
        BEGIN

            SET @Statement = 'UPDATE STATISTICS [' + @SchemaName + '].['
                + @tablename + ']' + @FullScanSQL

            SET @Status = 'Table ' + @TableNumber + ' of ' + @NumTables
                + ': Running ''' + @Statement + ''' (' + @RowsInTable
                + ' rows)'
            RAISERROR (@Status, 0, 1) WITH NOWAIT  --RAISERROR used to immediately output status

            EXEC sp_executesql @Statement

            FETCH NEXT FROM updatestats INTO @TableNumber, @SchemaName,
                @tablename, @RowsInTable
        END

    CLOSE updatestats
    DEALLOCATE updatestats

    DROP TABLE #TablesToUpdateStats

    PRINT 'Total Elapsed Time: ' + CONVERT(VARCHAR(100), DATEDIFF(minute,
                                                              @StartTime,
                                                              GETDATE()))
        + ' minutes'

    GO