Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

Update Stats with full scan on all tables in all databases Expand / Collapse
Author
Message
Posted Friday, September 14, 2012 2:14 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Sunday, May 4, 2014 11:25 AM
Points: 176, Visits: 406
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
Post #1359644
Posted Friday, September 14, 2012 2:31 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 7:09 PM
Points: 23,218, Visits: 31,905
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);





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)
Post #1359650
Posted Tuesday, September 18, 2012 11:17 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: 2 days ago @ 7:08 AM
Points: 1,283, Visits: 2,958

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.
Post #1360910
Posted Wednesday, September 19, 2012 8:13 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Yesterday @ 1:29 PM
Points: 4,343, Visits: 6,149
Ola.hallengren.com

Best maintenance stuff out there IMNSHO!!


Best,

Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru at GMail
Post #1361386
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse