T-SQL Script to Check the ‘Auto Update Statistics’ Option for Each DB in an Instance

/*
Script to check if AutoUpdateStats option is turned on for all the DB's in an instance.

Note: you have to take the output from the following script and run it again to get the final results.
*/

--SQL2000
select 'select ''' + name + ''' as DB_Name,  databasepropertyex(''' + name + ''', ''IsAutoUpdateStatistics'') as IsAutoUpdateStatisticsOn'
from master.dbo.sysdatabases

--SQL2005+
select 'select ''' + name + ''' as DB_Name,  databasepropertyex(''' + name + ''', ''IsAutoUpdateStatistics'') as IsAutoUpdateStatisticsOn'
from sys.databases

Advertisements

About Nick Xu

Not a DBA anymore
This entry was posted in T-SQL Script and tagged , . 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