Logo 
Search:

SQL Server Articles

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

DATABASEPROPERTYEX Function

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

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

DATABASEPROPERTYEX function is used to get current setting of the specified database option or property for the specified database.


Syntax of DATABASEPROPERTYEX Function :

DATABASEPROPERTYEX ( database, property )

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

property is an option or property setting to be returned. It is of type nvarchar(128). Below are the possible property names.

Value

Description

Returned Value

Collation

Default collation name for the database.

Collation name

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

IsAnsiPaddingEnabled

Strings are padded to the same length before comparison or insert.

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

IsArithmeticAbortEnabled

Queries are terminated when an overflow or divide-by-zero error occurs during query execution.

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

IsCloseCursorsOnCommitEnabled

Cursors that are open when a transaction is committed are closed.

1 = TRUE
0 = FALSE
NULL = Invalid input

IsFulltextEnabled

Database is full-text enabled.

1 = TRUE
0 = FALSE
NULL = 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

IsMergePublished

The tables of a database can be published for replication, if replication is installed.

1 = TRUE
0 = FALSE
NULL = Invalid input

IsNullConcat

Null concatenation operand yields NULL.

1 = TRUE
0 = FALSE
NULL = Invalid input

IsNumericRoundAbortEnabled

Errors are generated when loss of precision occurs in expressions.

1 = TRUE
0 = FALSE
NULL = Invalid input

IsPublished

The tables of the database can be published for snapshot or transactional replication, if replication is installed.

1 = TRUE
0 = FALSE
NULL = Invalid input

IsQuotedIdentifiersEnabled

Double quotation marks can be used on identifiers.

1 = TRUE
0 = FALSE
NULL = Invalid input

IsRecursiveTriggersEnabled

Recursive firing of triggers is enabled.

1 = TRUE
0 = FALSE
NULL = Invalid input

IsSubscribed

Database can be subscribed for publication.

1 = TRUE
0 = FALSE
NULL = Invalid input

IsTornPageDetectionEnabled

Microsoft® SQL Server™ detects incomplete I/O operations caused by power failures or other system outages.

1 = TRUE
0 = FALSE
NULL = Invalid input

Recovery

Recovery model for the database.

FULL = full recovery model
BULK_LOGGED = bulk logged model
SIMPLE = simple recovery model

SQLSortOrder

SQL Server sort order ID supported in previous versions of SQL Server.

0 = Database is using Windows collation
>0 = SQL Server sort order ID

Status

Database status.

ONLINE = database is available for query
OFFLINE = database was explicitly taken offline
RESTORING = database is being restored
RECOVERING = database is recovering and not yet ready for queries
SUSPECT = database cannot be recovered

Updateability

Indicates whether data can be modified.

READ_ONLY = data can be read but not modified
READ_WRITE = data can be read and modified

UserAccess

Indicates which users can access the database.

SINGLE_USER = only onedb_ownerdbcreator, orsysadmin user at a time
RESTRICTED_USER = only members of db_owner,dbcreator, and sysadminroles
MULTI_USER = all users

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 DATABASEPROPERTYEX function is a sql_variant.



Examples of DATABASEPROPERTYEX Function :

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

SELECT DATABASEPROPERTYEX('Northwind', 'IsAutoClose')

Output
0

Above example returns 0 means ISAutoClose property is false or not enabled for NorthWind database. 



Example 2 : Use of DATABASEPROPERTYEX function to get database information in select clause

SELECT DATABASEPROPERTYEX('Northwind', 'Collation')

Output
SQL_Latin1_General_CP1_CI_AS

Above example returns name of the default collation for the Northwind database.
  
Share: 

 
 
 

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

Sarita Patel
Sarita Patel author of DATABASEPROPERTYEX 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!