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 > 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

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;

StackExchange Database Administrators Q#81607, answer score: 5