Update statistics for all databases all tables in a server 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 &gt; 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 &lt;= @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 &lt;= @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*/
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;