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;