Check If Auto Update Statistics Is Enabled By Using DATABASEPROPERTY
From Wiki
How do you check if auto update statistics is enabled on your database? It is pretty easy to check that, you can use the DATABASEPROPERTY function Run the following line of code
- SELECT DATABASEPROPERTY('pubs','IsAutoUpdateStatistics') AS IsAutoUpdateStatistics
If 1 is returned(true) it is enabled, if 0 is returned(false) then it is not enabled
Now to save me (and you) time I have pasted a code block below with all the properties, just change the database name from pubs to your database name and run the code
- DECLARE @v VARCHAR(55)
- SELECT @v = 'pubs'
- SELECT
- DATABASEPROPERTY(@v,'IsAnsiNullDefault') AS IsAnsiNullDefault,
- DATABASEPROPERTY(@v,'IsAnsiNullsEnabled') AS IsAnsiNullsEnabled,
- DATABASEPROPERTY(@v,'IsAnsiWarningsEnabled') AS IsAnsiWarningsEnabled,
- DATABASEPROPERTY(@v,'IsAutoClose') AS IsAutoClose,
- DATABASEPROPERTY(@v,'IsAutoCreateStatistics') AS IsAutoCreateStatistics,
- DATABASEPROPERTY(@v,'IsAutoShrink') AS IsAutoShrink,
- DATABASEPROPERTY(@v,'IsAutoUpdateStatistics') AS IsAutoUpdateStatistics,
- DATABASEPROPERTY(@v,'IsBulkCopy') AS IsBulkCopy,
- DATABASEPROPERTY(@v,'IsDboOnly') AS IsDboOnly,
- DATABASEPROPERTY(@v,'IsDetached') AS IsDetached,
- DATABASEPROPERTY(@v,'IsEmergencyMode') AS IsEmergencyMode,
- DATABASEPROPERTY(@v,'IsInLoad') AS IsInLoad,
- DATABASEPROPERTY(@v,'IsInRecovery') AS IsInRecovery,
- DATABASEPROPERTY(@v,'IsAutoClose') AS IsAutoClose,
- DATABASEPROPERTY(@v,'IsInStandBy') AS IsInStandBy,
- DATABASEPROPERTY(@v,'IsLocalCursorsDefault') AS IsLocalCursorsDefault,
- DATABASEPROPERTY(@v,'IsNotRecovered') AS IsNotRecovered,
- DATABASEPROPERTY(@v,'IsNullConcat') AS IsNullConcat,
- DATABASEPROPERTY(@v,'IsOffline') AS IsOffline,
- DATABASEPROPERTY(@v,'IsQuotedIdentifiersEnabled') AS IsQuotedIdentifiersEnabled,
- DATABASEPROPERTY(@v,'IsReadOnly') AS IsReadOnly,
- DATABASEPROPERTY(@v,'IsRecursiveTriggersEnabled') AS IsRecursiveTriggersEnabled,
- DATABASEPROPERTY(@v,'IsShutDown') AS IsShutDown,
- DATABASEPROPERTY(@v,'IsSingleUser') AS IsSingleUser,
- DATABASEPROPERTY(@v,'IsSuspect') AS IsSuspect,
- DATABASEPROPERTY(@v,'IsTruncLog') AS IsTruncLog,
- DATABASEPROPERTY(@v,'Version') AS Version
So what do all these values mean? Here is a list of all the properties
IsAnsiNullDefault Database follows SQL-92 rules for allowing null values.
1 = TRUE, 0 = FALSE, NULL = Invalid input
IsAnsiNullsEnabled
All comparisons to a null evaluate to unknown.
1 = TRUE, 0 = FALSE, NULL = Invalid input
IsAnsiWarningsEnabled
Error or warning messages are issued when standard error conditions occur.
1 = TRUE, 0 = FALSE, NULL = Invalid input
IsAutoClose
Database shuts down cleanly and frees resources after the last user exits.
1 = TRUE, 0 = FALSE, NULL = Invalid input
IsAutoCreateStatistics
Existing statistics are automatically updated when the statistics become out-of-date because the data in the tables has changed.
1 = TRUE, 0 = FALSE, NULL = Invalid input
IsAutoShrink
Database files are candidates for automatic periodic shrinking.
1 = TRUE, 0 = FALSE, NULL = Invalid input
IsAutoUpdateStatistics
Auto update statistics database option is enabled.
1 = TRUE, 0 = FALSE, NULL = Invalid input
IsBulkCopy
Database allows nonlogged operations.
1 = TRUE, 0 = FALSE, NULL = Invalid input
IsCloseCursorsOnCommitEnabled
Cursors that are open when a transaction is committed are closed.
1 = TRUE, 0 = FALSE, NULL = Invalid input
IsDboOnly
Database is in DBO-only access mode.
1 = TRUE, 0 = FALSE, NULL = Invalid input
IsDetached
Database was detached by a detach operation.
1 = TRUE, 0 = FALSE, NULL = Invalid input
IsEmergencyMode
Emergency mode is enabled to allow suspect database to be usable.
1 = TRUE, 0 = FALSE, NULL = Invalid input
IsFulltextEnabled
Database is full-text enabled.
1 = TRUE, 0 = FALSE, NULL = Invalid input
IsInLoad
Database is loading.
1 = TRUE, 0 = FALSE, NULL = Invalid input
IsInRecovery
Database is recovering.
1 = TRUE, 0 = FALSE, NULL1 = Invalid input
IsInStandBy
Database is online as read-only, with restore log allowed.
1 = TRUE, 0 = FALSE, NULL = Invalid input
IsLocalCursorsDefault
Cursor declarations default to LOCAL.
1 = TRUE, 0 = FALSE, NULL = Invalid input
IsNotRecovered
Database failed to recover.
1 = TRUE, 0 = FALSE, NULL = Invalid input
IsNullConcat
Null concatenation operand yields NULL.
1 = TRUE, 0 = FALSE, NULL = Invalid input
IsOffline
Database is offline.
1 = TRUE, 0 = FALSE, NULL = Invalid input
IsQuotedIdentifiersEnabled
Double quotation marks can be used on identifiers.
1 = TRUE, 0 = FALSE, NULL = Invalid input
IsReadOnly
Database is in a read-only access mode.
1 = TRUE, 0 = FALSE, NULL = Invalid input
IsRecursiveTriggersEnabled
Recursive firing of triggers is enabled.
1 = TRUE, 0 = FALSE, NULL = Invalid input
IsShutDown
Database encountered a problem at startup.
1 = TRUE, 0 = FALSE, NULL1 = Invalid input
IsSingleUser
Database is in single-user access mode.
1 = TRUE, 0 = FALSE, NULL = Invalid input
IsSuspect
Database is suspect.
1 = TRUE, 0 = FALSE, NULL = Invalid input
IsTruncLog
Database truncates its logon checkpoints.
1 = TRUE, 0 = FALSE, NULL = Invalid input
Version
Internal version number of the Microsoft® SQL Server™ code
Contributed by: --SQLDenis 16:25, 6 June 2008 (GMT)
Part of SQL Server Admin Hacks
