Create SQL Script to update stats for all tables in a database with Full Scan

SSUGE.org

I was asked to run update stats on all the tables in a database with full scan.
And I was to use native SQL code not OLA or another open source.
Additional requirement was to update stats on each table individually listed as a separate line of code.

The database had multiple tables so to list each individual items was going to very burdensome.

Thankfully I found this sql script that will generate an update stat statement for each table in the database. You just have to copy the query output to either a new query window or just put it in a sql job and run it.

SET NOCOUNT ON
GO
 
DECLARE updatestats CURSOR FOR
SELECT table_schema, table_name
FROM information_schema.tables
where TABLE_TYPE = 'BASE TABLE'
OPEN updatestats
 
DECLARE @tableSchema NVARCHAR(128)
DECLARE @tableName NVARCHAR(128)
DECLARE @Statement NVARCHAR(300)
 
FETCH NEXT FROM updatestats INTO @tableSchema, @tableName
 
WHILE (@@FETCH_STATUS = 0)
BEGIN
  SET @Statement = 'UPDATE STATISTICS '  + '[' + @tableSchema + ']' + '.' + '[' + @tableName + ']' + ' WITH FULLSCAN'
  PRINT @Statement -- comment this line out if you want to actually run the query instead of just getting the code for the task.
 --EXEC sp_executesql @Statement -- remove the comment to run the command 
  FETCH NEXT FROM updatestats INTO @tableSchema, @tableName
END
 
CLOSE updatestats
DEALLOCATE updatestats
GO
SET NOCOUNT

Hope this helps, enjoy!