The DBA Blog by Tomer Lev

SQL information, scripts, tricks and more…

Update statistics All Tables Script


SET NOCOUNT ON
GO
DECLARE updatestats CURSOR FOR
SELECT table_name FROM information_schema.tables
where TABLE_TYPE = ‘BASE TABLE’
OPEN updatestats

DECLARE @tablename NVARCHAR(128)
DECLARE @Statement NVARCHAR(300)

FETCH NEXT FROM updatestats INTO @tablename
WHILE (@@FETCH_STATUS = 0)
BEGIN
PRINT N’UPDATING STATISTICS ‘ + @tablename
SET @Statement = ‘UPDATE STATISTICS ‘  + @tablename –+ ‘  WITH FULLSCAN’
–print @Statement
EXEC sp_executesql @Statement
FETCH NEXT FROM updatestats INTO @tablename
END

CLOSE updatestats
DEALLOCATE updatestats
GO
SET NOCOUNT OFF
GO

Advertisements

June 22, 2009 - Posted by | Administration, Scripts, SQL SERVER | , ,

No comments yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: