update statistics - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SQL Server Administration (2005)

update statistics

Author

Topic

DBA007
Posting Yak Master

145 Posts

Posted - 2010-06-14 : 14:54:33

HI,my maintenanceplan was failing,i have desabled it,i want to create a script to update statistics and loop all databases

robvolk
Most Valuable Yak

15732 Posts

Posted - 2010-06-14 : 15:13:50

EXEC sp_msforeachdb 'EXEC [ ? ].sys.sp_updatestats'

Remove the spaces from around the ? symbol before you run this.

DBA007
Posting Yak Master

145 Posts

Posted - 2010-06-14 : 16:03:33

Thanks,robvolk
but i need the script to be run as a job dialy looping through all dbs

robvolk
Most Valuable Yak

15732 Posts

Posted - 2010-06-14 : 16:21:54

That's what I posted. The sp_msforeachdb proc runs across all databases, and sp_updatestats updates stats on all tables. Just paste that into a SQL job step and you're all set.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-06-14 : 16:49:56

Here's the code we use:



SET NOCOUNT ON

DECLARE @dbName sysname, @rc int, @sql nvarchar(4000)

SELECT name
INTO #db
FROM sys.databases
WHERE 
	name NOT IN ('master', 'model', 'msdb', 'tempdb', 'Admin', 'DBA', 'OnePoint') AND
	name NOT LIKE '%ReportServer%' AND
	DATABASEPROPERTYEX([name], 'IsInStandBy') = 0 AND
	DATABASEPROPERTYEX([name], 'Status') = 'ONLINE' AND
	DATABASEPROPERTYEX([name], 'Updateability') = 'READ_WRITE' AND
	source_database_id IS NULL

SET @rc = @@ROWCOUNT

SELECT @dbName = MIN(name)
FROM #db

WHILE @rc <> 0
BEGIN
	SET @sql = 'EXEC ' + QUOTENAME(@dbName) + '.dbo.sp_updatestats @resample = ''resample'''

	EXEC (@sql)

	SELECT TOP 1 @dbName = name
	FROM #db
	WHERE name > @dbName
	ORDER BY name

	SET @rc = @@ROWCOUNT
END

DROP TABLE #db

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

DBA007
Posting Yak Master

145 Posts

Posted - 2010-06-15 : 18:00:07

Thanks Tara

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-06-15 : 18:12:13

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources