Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Update Stats with full scan on all tables in all databases


Update Stats with full scan on all tables in all databases

Author
Message
DBA_Learner
DBA_Learner
SSC-Enthusiastic
SSC-Enthusiastic (199 reputation)SSC-Enthusiastic (199 reputation)SSC-Enthusiastic (199 reputation)SSC-Enthusiastic (199 reputation)SSC-Enthusiastic (199 reputation)SSC-Enthusiastic (199 reputation)SSC-Enthusiastic (199 reputation)SSC-Enthusiastic (199 reputation)

Group: General Forum Members
Points: 199 Visits: 450
You can the following query instead of the one mentioned. I see your query is consuming more cpu which is not good for prod.

Hope you can use my query.

SET NOCOUNT ON
DECLARE @SQLcommand NVARCHAR(512),
@Table SYSNAME
DECLARE curAllTables CURSOR FOR
SELECT table_schema + '.' + table_name
FROM information_schema.tables
WHERE TABLE_TYPE = 'BASE TABLE'
OPEN curAllTables
FETCH NEXT FROM curAllTables
INTO @Table
WHILE (@@FETCH_STATUS = 0)
BEGIN
PRINT N'UPDATING STATISTICS FOR TABLE: ' + @Table
SET @SQLcommand = 'UPDATE STATISTICS ' + @Table + ' WITH FULLSCAN'
EXEC sp_executesql
@SQLcommand
FETCH NEXT FROM curAllTables
INTO @Table
END
CLOSE curAllTables
DEALLOCATE curAllTables
SET NOCOUNT OFF
Lynn Pettis
Lynn Pettis
SSC-Insane
SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)

Group: General Forum Members
Points: 24177 Visits: 37948
Or this:



eclare @SQLCmd varchar(max);

select
@SQLCmd = (select 'UPDATE STATISTICS ' + object_name(object_id) + ' with fullscan;' + char(13) + char(10)
from
sys.tables
for xml path (''),type).value('.','varchar(max)');

print @SQLCmd;
exec (@SQLCmd);




Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
curious_sqldba
curious_sqldba
SSCommitted
SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)

Group: General Forum Members
Points: 1543 Visits: 3573
My two cents. If you are looking for something to run in parallel across all databases, you could either use Red Gate multiscript tool ( worth every penny for this one !!!) or use this which will need little manual work but this also works great.
TheSQLGuru
TheSQLGuru
SSCertifiable
SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)

Group: General Forum Members
Points: 5947 Visits: 8303
Ola.hallengren.com

Best maintenance stuff out there IMNSHO!! :-)

Best,

Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru at GMail
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search