Update the statistics to all tables in a database WITH FULLSCAN


DECLARE @SQL NVARCHAR (MAX)
DECLARE C1 CURSOR FOR
SELECT DISTINCT ‘UPDATE STATISTICS [dbo].[‘+o.name +’] WITH FULLSCAN ‘

FROM sys.objects AS o WITH (NOLOCK)
INNER JOIN sys.indexes AS i WITH (NOLOCK)
ON o.[object_id] = i.[object_id]
INNER JOIN sys.stats AS s WITH (NOLOCK)
ON i.[object_id] = s.[object_id]
AND i.index_id = s.stats_id
INNER JOIN sys.dm_db_partition_stats AS st WITH (NOLOCK)
ON o.[object_id] = st.[object_id]
AND i.[index_id] = st.[index_id]
WHERE o.[type] = ‘U’ and ISNULL (STATS_DATE(i.[object_id], i.index_id), ’01/011/2014′) < '01/31/2014'
OPEN C1
FETCH NEXT FROM C1
INTO @SQL
WHILE @@FETCH_STATUS = 0
BEGIN
RAISERROR (@sql, 10, 1) 
EXEC (@SQL)
FETCH NEXT FROM C1
INTO @SQL
END
CLOSE C1
DEALLOCATE C1

Advertisements
This entry was posted in Uncategorized. Bookmark the permalink.

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