Select = table_name from table_count where rownum = ‘UPDATE STATISTICS ‘ + ‘’ + ‘ WITH FULLSCAN’ tables where table_type = ‘base table’ĭeclare int, varchar ( 255 ), int, varchar ( 50 ) Select identity ( int, 1, 1 ) as rownum, table_name into table_count from information_schema. You have to un-comment the comments lines, to actually perform the update. I am using FULL SCAN by default, which you can of course modify to use specific SAMPLE. You can use this script to generate the UPDATE STATISTICS command for each table in your table. But, you can rely on last updated time to make a decision if the statistics need to be updated. Keep in mind that Rowmodctr values displayed in above output are not entirely reliable starting with SQL 2005 onwards. status not in ( 8388704, 8388672 )Īnd ( select max ( rowcnt ) from sysindexes i2 where i. ( select max ( rowcnt ) from sysindexes i2 where i. You can always use DBCC SHOW_STATISTICS or SP_AUTOSTATS to find out the same information for a specific table, index or column stats.

This can be quite handy if you are managing multiple SQL instances and you have lot of tables or have large databases where you only want to update statistics on some tables based on row modifications (data changes e.g. I am re-posting this here for your reference. I made some changes to the script to make it easier for DBA’s to read the output. I wrote a post on the same topic Amit’s Blog.

Update statistics sql server