Update statistics for all databases all tables in a server

Question

Hi I have been working on a script to update statistics of all tables on all my DBs. The idea is to parametrise it later on, but just as a quick fix, and not wanting to implement Ola's scripts today, follow the script below.

I have tested it on a few servers, but before I schedule it to run on a live server Sunday morning, would like to get some ideas and share it with you.

SET NOCOUNT ON

DECLARE @DBS TABLE (I INT NOT NULL IDENTITY(1,1) PRIMARY KEY CLUSTERED,
                    DBNAME SYSNAME NOT NULL )

DECLARE @I INT
        ,@Z INT
        ,@SQL VARCHAR(1008) 
        ,@j INT
        ,@Y INT
        ,@MYDB SYSNAME

INSERT INTO @DBS
SELECT s.name 
FROM sys.databases s
INNER JOIN SYS.master_files F ON S.DATABASE_ID = F.database_id  AND F.data_space_id = 1
WHERE S.STATE = 0 -- online
  AND S.database_id > 3 -- exclude master, tempdb and model -- I left msdb 
  AND S.is_read_only = 0 -- read/write
  AND S.user_access = 0 -- multi_user
  AND S.NAME NOT IN ('TableBackups',
                     'Troubleshooting')
ORDER BY F.SIZE DESC


SELECT @Z = @@ROWCOUNT
SELECT @I = 1



WHILE @I <= @z BEGIN


    BEGIN TRY DROP TABLE #T END TRY BEGIN CATCH END CATCH
    CREATE TABLE #T( I INT NOT NULL IDENTITY(1,1) PRIMARY KEY CLUSTERED, MYSQL VARCHAR(1008) NOT NULL )

    SELECT @MYDB = QUOTENAME (S.DBNAME )
           ,@SQL = 'USE ' + QUOTENAME (S.DBNAME ) + ';' + CHAR(13) + ' 
                    ' + CHAR(13) +
                   'INSERT INTO #T(MYSQL)  SELECT ''UPDATE STATISTICS '' + QUOTENAME(NAME) FROM SYS.TABLES WHERE TYPE = ''U'' ' + CHAR(13)  

      FROM @DBS s
        WHERE s.I = @I 


    PRINT CAST (@SQL AS NTEXT)
       BEGIN TRY

            EXEC (@SQL)
            SELECT @Y = @@ROWCOUNT

       END TRY
       BEGIN CATCH
           SELECT @Y = 0
       END CATCH



    SELECT @J = 1

    WHILE @J <= @y BEGIN

       SELECT @SQL = 'USE ' + @MYDB + ';' + CHAR(13) + MYSQL 
       FROM #T 
       WHERE I= @J

       BEGIN TRY

            EXEC (@SQL)

       END TRY
       BEGIN CATCH
       END CATCH


       PRINT CAST (@SQL AS NTEXT)

       SELECT @J = @J + 1

    END 

    SELECT @I = @I +1

END /*WHILE*/

Answer 1

Right off the bat, your script completely ignores schema, so it will fail unless all of your production servers only use dbo explicitly as the namespace for all tables (and nobody has a default schema other than dbo). You're also using a deprecated data type (NTEXT) for seemingly no reason. Also I don't think the WHERE clause against sys.tables is necessary (I don't know how anything but a U could ever be in there). Not sure why you bother ordering by file size; the job isn't going to finish faster if you handle the biggest databases first (also the join could return multiple rows for a database if it has more than one file in data_space_id = 1, which means you would potentially process that database multiple times).

Finally, I have to wonder if it is really wise to blindly update stats on every table in every database across your entire instance, regardless of size... and why you don't bother checking for indexed views, which also require maintenance. You could save quite a bit of work if you add some logic to exclude any tables that don't need it.

My partial solution

This code will be slightly harder to maintain, doesn't have the TRY/CATCH wrappers yours does, and doesn't add any additional logic about which tables to actually pursue, but since I get a kick out of making automation tasks out of dynamic SQL (and really enjoy solutions that cause no horizontal or vertical scrollbars on this site), here you go. When you're happy with the output, comment the print and uncomment the exec.

DECLARE @sql NVARCHAR(MAX) = N'', @stats NVARCHAR(MAX) = N'';

SELECT @sql += N'EXEC ' + QUOTENAME(name) + '.sys.sp_executesql @stats;'
  FROM sys.databases 
  WHERE [state] = 0 AND user_access = 0; -- and your other filters

SET @stats = N'DECLARE @inner NVARCHAR(MAX) = N''''; 
  SELECT @inner += CHAR(10) + N''UPDATE STATISTICS '' 
    + QUOTENAME(s.name) + ''.'' + QUOTENAME(t.name) + '';'' 
    FROM sys.tables AS t
    INNER JOIN sys.schemas AS s 
    ON t.[schema_id] = s.[schema_id];
  PRINT CHAR(10) + DB_NAME() + CHAR(10) + @inner;
  --EXEC sys.sp_executesql @inner;'

EXEC [master].sys.sp_executesql @sql, N'@stats NVARCHAR(MAX)', @stats;

Answer 2

Unless you want to implement some really powerful filters in your logic, the following code will do the job just fine:

exec sp_MSforeachdb 'use [?]; if db_name() not in (''master'', ''model'', ''tempdb'', ''msdb'') exec sp_updatestats;';
go

Answer 3

I did notice a couple of things.

1. I highly recommend using more than one character variable & temp table names. It makes it harder to read and next year when you are reading this again (or someone else is) you won't remember what they mean. It's not that hard to use @DBCounter instead of @I for example.

2. Your DB list query at the top will create duplicates (causing duplicate work) if there is more than one data filegroup. Also you won't be looking at the DB size, just the file size in your ordering. I would change the end of the query to this:

   GROUP BY s.name
   ORDER BY SUM(F.SIZE) DESC
   

3. Is there a reason you aren't using sp_updatestats on each database rather than running UPDATE STATISTICS on each table? It also has the benefit of only updating the statistics on tables that need to have it run.

4. Last thing and not a huge issue. Don't CAST to NTEXT. It's an old data type and will be removed in a future version of SQL Server. Use NVARCHAR(MAX) instead.