Login or Sign Up to become a member!

Less Than Dot is a community of passionate IT professionals and enthusiasts dedicated to sharing technical knowledge, experience, and assistance. Inside you will find reference materials, interesting technical discussions, and expert tips and commentary. Once you register for an account you will have immediate access to the forums and all past articles and commentaries.

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

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

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