update stats with full scan on database

Question

Hi All,

Is it possible to take update statics with full scan on database level instead of specifying table or index name.

If it is possible please provide me the script.

Thanks

Krish735

Answer 1

The BOL on the Update Statistics says to use sp_updatestats

http://msdn.microsoft.com/en-us/library/ms187348.aspx

execute sp_updatestats

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog

Answer 2

Just like Naomi suggested use sp_updatestats but use the resample argument:

so: exec sp_updatestats 'resample'

Answer 3

See this

http://msdn.microsoft.com/en-us/library/ms187348.aspx

---

Many Thanks & Best Regards, Hua Min

Answer 4

Thanks for your responses.

Please provide me a script to update statistics on all the tables and index  in a database with full scan.

Thanks

Krish

Answer 5

Thanks for your responses.

Please provide me a script to update statistics on all the tables and index  in a database with full scan.

Thanks

Krish

USE AdventureWorks2012;
GO
UPDATE STATISTICS Production.Product(Products)
    WITH FULLSCAN, NORECOMPUTE;
GO

---

Many Thanks & Best Regards, Hua Min

Answer 6

the example updates statistics only on a single table.

I want update statistics on all the tables and indexes in a database

Answer 7

Hi Krish, as I said you can use the sp_updatestats to update stats for all tables in a database, but in order to do the full scan you need to include the resample argument. so to to update statistics on all the tables and index  in a database with full scan, use this:

exec sp_updatestats 'resample'

Answer 8

the example updates statistics only on a single table.

I want update statistics on all the tables and indexes in a database

Syntax

---

          

sp_updatestats [ [ @resample = ] 'resample']
        

Return Code Values

---

0 (success) or 1 (failure)

Arguments

---

[ @resample =] 'resample'

Specifies that sp_updatestats will use the RESAMPLE option of the UPDATE STATISTICS statement. If 'resample' is not specified, sp_updatestats updates statistics by using the default sampling. resample is varchar(8) with a default value of NO.

---

Many Thanks & Best Regards, Hua Min

Answer 9

Some people suggested sp_updatestats, but sp_updatetats has no guaranteed way to result in a FULLSCAN for all tables.

But you can do:

DECLARE @sql nvarchar(MAX);
SELECT @sql = (SELECT 'UPDATE STATISTICS ' +
                      quotename(s.name) + '.' + quotename(o.name) +
                      ' WITH FULLSCAN; ' AS [text()]
               FROM   sys.objects o
               JOIN   sys.schemas s ON o.schema_id = s.schema_id
               WHERE  o.type = 'U'
               FOR XML PATH(''), TYPE).value('.', 'nvarchar(MAX)');
PRINT @sql
EXEC (@sql)

---

Erland Sommarskog, SQL Server MVP, [e-mail]

Answer 10

use the below query

EXEC sp_MSForEachTable 'UPDATE STATISTICS ? WITH FULLSCAN;'

Best Regards,

Sandip Pani (MCTS, MCITP)

SQLCommitted

---

Sandip Pani http://SQLCommitted.com

Answer 11

Yes it is, I am using the below script

DECLARE @tablename varchar(80),@shemaname varchar(80)
DECLARE @SQL AS NVARCHAR(200)
DECLARE TblName_cursor CURSOR FOR
SELECT t.name,s.name FROM sys.tables t join sys.schemas s
on s.schema_id=t.schema_id


OPEN TblName_cursor

FETCH NEXT FROM TblName_cursor
INTO @tablename,@shemaname

WHILE @@FETCH_STATUS = 0
BEGIN
SET @SQL = 'UPDATE STATISTICS '+@shemaname+'.[' + @TableName + '] WITH FULLSCAN ' ---+ CONVERT(varchar(3), @sample) + ' PERCENT'

EXEC sp_executesql @statement = @SQL

   FETCH NEXT FROM TblName_cursor
   INTO @tablename,@shemaname
END

CLOSE TblName_cursor
DEALLOCATE TblName_cursor

---

Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/