Dynamically Updating Statistics on all Databases, With Sweet Potato Fries.

Dynamically Update Statistics on All Databases on a server

--statistics maintenance
--updates anything with more than 2% of changes
--or anything not updated within last 30 days
--This does full scan, because we have the time in our maintenance window
--This should be run after any index rebuilding as a statistics should get a update with a full scan
--Sidenote: If you have partitioned indexes and are running SQL 2012 or later, you should read up on how a index rebuild does not use fullscan
--And if you are using paritions, and switching partitions, you also may need to manually update your statistics

SET NOCOUNT ON;

DECLARE @command nvarchar(4000);
DECLARE @UpdateStatement VarChar(2000);
DECLARE @dbid smallint;

--for each database on server
DECLARE @DBs TABLE (id int identity(1,1), name varchar(255));
DECLARE @DBName varchar(255);
DECLARE @StatsForDB varchar(4000); --table to hold dynamic sql 
CREATE TABLE #WorkToDo (UpdateStatement varchar(1000));

INSERT INTO @DBs SELECT name FROM sys.databases
WHERE database_id > 4 --not system db
AND state <> 6; --not offline 

DECLARE @Cnt INT = 1;
WHILE @Cnt < (SELECT COUNT(1) FROM @DBs) + 1
BEGIN

	SET @DBName = (SELECT name FROM @DBs WHERE id = @Cnt)
	SET @dbid = DB_ID(@DBName);

	SET @StatsForDB = 'INSERT INTO #WorkToDo select distinct ''update statistics ['+ @DBName + '].['' + stats.SchemaName + ''].['' + stats.TableName + ''] WITH FULLSCAN;''
    from (
        select
            ss.name SchemaName,
            so.name TableName,
            so.id ObjectId,
            st.name AS StatsName, 
            STATS_DATE(st.object_id, st.stats_id) AS LastStatisticsUpdateDate
            , si.RowModCtr
            , (select case si2.RowCnt when 0 then 1 else si2.RowCnt end from ' + @DBName + '..sysindexes si2 where si2.id = si.id and si2.indid in (0,1)) RowCnt
        from ' + @DBName + '.sys.stats st
            join ' + @DBName + '..sysindexes si on st.object_id = si.id and st.stats_id = si.indid
            join ' + @DBName + '..sysobjects so on so.id = si.id and so.xtype = ''U'' --user table
            join ' + @DBName + '.sys.schemas ss on ss.schema_id = so.uid
    ) stats
    where cast(stats.RowModCtr as float)/cast(stats.RowCnt as FLOAT)*100 >= 2 --more than 2% of the rows have changed
    or ( --over 1 month old (and row count > 0)
        datediff(day, stats.LastStatisticsUpdateDate, getdate()) >= 30
        and stats.RowCnt > 0
    );'
	EXEC (@StatsForDB)
	SET @Cnt = @Cnt + 1
END

	IF NOT EXISTS (SELECT 1 FROM #WorkToDo) PRINT 'No statistics to update.'

	DECLARE updatestats CURSOR FOR SELECT UpdateStatement FROM #WorkToDo;
	OPEN updatestats;

	WHILE (1=1)
	BEGIN
	FETCH NEXT FROM updatestats INTO @UpdateStatement;
	
		IF @@FETCH_STATUS <> 0 BREAK;

		SET @command = @UpdateStatement;
		PRINT N'Executing: ' + @command;
		EXEC (@command);

		END

	CLOSE updatestats;
	DEALLOCATE updatestats;

	DROP TABLE #WorkToDo;
GO