April 24, 2008 at 5:45 am
Hi,
I have 10 Databases, i need to Update Statitistics in All Tables.
Shall I use this Query
sp_msforeachdb 'use ?;
exec sp_updatestats
go
Any other Suggestions
April 24, 2008 at 6:21 am
If you are using SQL 2005 and above, you can go ahead, but for versions before 9.0, executing sp_updatestats resets the automatic UPDATE STATISTICS setting for all indexes and statistics on every table in the current database....so be careful.....
Chandrachurh Ghosh
DBA – MS SQL Server
Ericsson India Global Services Limited
Quality is not an act, it is a habit.
April 24, 2008 at 6:31 am
Better you use the following statement -
DECLARE DB CURSOR
FOR SELECT [name]
FROM SYS.DATABASES
WHERE owner_sid<>0x01
FOR READ ONLY
DECLARE @DB_NAMEVARCHAR(100),
@STR_SQLVARCHAR(20),
@STR_DBVARCHAR(150)
OPEN DB
FETCH NEXT FROM DB INTO @DB_NAME
WHILE @@FETCH_STATUS=0
BEGIN
SET @STR_DB='USE ['+@DB_NAME+']'
SET @STR_SQL=' EXEC sp_updatestats'
EXEC(@STR_DB+@STR_SQL)
FETCH NEXT FROM DB INTO @DB_NAME
END
CLOSE DB
DEALLOCATE DB
.......and then check the messages
Chandrachurh Ghosh
DBA – MS SQL Server
Ericsson India Global Services Limited
Quality is not an act, it is a habit.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply