Update statistics of all tables in SQL Server 2008/2005

Thursday, May 14, 2009 |

Today I am going to share very small yet very useful and handy script. It is often necessary to keep update statistics of all tables for performance point of view. Actually by default query optimizer updates statistics in query plan in certain situation but it is good to manually do it to assure that every query plan it optimized and up-to-date.

You can generate T-SQL for update statistics for all tables of your database along with Schema name. Have a look at the script.

SELECT
        'UPDATE STATISTICS ' + SysSche.Name + '.' + SysObj.Name
FROM
        Sys.Objects SysObj
INNER JOIN
        sys.schemas SysSche ON SysObj.Schema_ID = SysSche.Schema_ID
WHERE
        TYPE = 'U'


For more details about UPDATE STATESTICS and its syntax, you can refer official Microsoft link at


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

 

Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of
http://www.SQLHub.com

0 comments: