Logo 
Search:

SQL Server Articles

Submit Article
Home » Articles » SQL Server » Meta Data FunctionsRSS Feeds

DATABASEPROPERTY Function

Posted By: Sarita Patel     Category: SQL Server     Views: 2968

This article explains about DATABASEPROPERTY function in sql server with examples.

DATABASEPROPERTY function is used to get information about specified database property value.


Syntax of DATABASEPROPERTY Function :

DATABASEPROPERTY ( database, property )

database is a name of the database. It is of type nvarchar(128) 

property is a name of database property to return. It is of type varchar(128). Below are the possible property names.

Value

Description

Returned Value

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 with which the database was created. For internal use only by SQL Server tools and in upgrade processing.

Version number = Database is open
NULL = Database is closed

 

 Return type of DATABASEPROPERTY function is an integer.




Examples of DATABASEPROPERTY Function :

Example 1 : Use of DATABASEPROPERTY function to get database information in select clause

SELECT DATABASEPROPERTY('NorthWind','IsSuspect')

Output
0

Above example returns 0 means NorthWind database is not suspected.
  
Share: 


Didn't find what you were looking for? Find more on DATABASEPROPERTY Function Or get search suggestion and latest updates.

Sarita Patel
Sarita Patel author of DATABASEPROPERTY Function is from United States.
 
View All Articles

Related Articles and Code:


 
Please enter your Comment

  • Comment should be atleast 30 Characters.
  • Please put code inside [Code] your code [/Code].

 
No Comment Found, Be the First to post comment!