DATABASEPROPERTYEX (Transact-SQL)

Applies to: SQL Server Azure SQL Database Azure SQL Managed Instance Azure Synapse Analytics Analytics Platform System (PDW) SQL database in Microsoft Fabric Preview

For a specified database in SQL Server, the DATABASEPROPERTYEX function returns the current setting of the specified database option or property.

Transact-SQL syntax conventions

Syntax

DATABASEPROPERTYEX ( database , property )

Arguments

database

An expression specifying the name of the database for which DATABASEPROPERTYEX returns the named property information. database has an nvarchar(128) data type.

For SQL Database, DATABASEPROPERTYEX requires the name of the current database. It returns NULL for all properties if given a different database name.

property

An expression specifying the name of the database property to return. property has a varchar(128) data type, and supports one of the values in this table:

Return types

sql_variant

Exceptions

Returns NULL on error, or if a caller doesn't have permission to view the object.

In SQL Server, a user can only view the metadata of securables that the user owns or on which the user has been granted permission. This rule means that metadata-emitting, built-in functions such as OBJECT_ID can return NULL if the user has no permissions on the object. For more information, see Metadata visibility configuration.

Remarks

DATABASEPROPERTYEX returns only one property setting at a time. To display multiple property settings, use the sys.databases catalog view.

Examples

The code samples in this article use the AdventureWorks2022 or AdventureWorksDW2022 sample database, which you can download from the Microsoft SQL Server Samples and Community Projects home page.

A. Retrieve the status of the AUTO_SHRINK database option

This example returns the status of the AUTO_SHRINK database option for the AdventureWorks database.

SELECT DATABASEPROPERTYEX('AdventureWorks2022', 'IsAutoShrink');

Here's the result set. This indicates that AUTO_SHRINK is off.

0

B. Retrieve the default collation for a database

This example returns several attributes of the AdventureWorks database.

SELECT DATABASEPROPERTYEX('AdventureWorks2022', 'Collation') AS Collation,
       DATABASEPROPERTYEX('AdventureWorks2022', 'Edition') AS Edition,
       DATABASEPROPERTYEX('AdventureWorks2022', 'ServiceObjective') AS ServiceObjective,
       DATABASEPROPERTYEX('AdventureWorks2022', 'MaxSizeInBytes') AS MaxSizeInBytes;

Here's the result set.

Collation                     Edition        ServiceObjective  MaxSizeInBytes
----------------------------  -------------  ----------------  --------------
SQL_Latin1_General_CP1_CI_AS  DataWarehouse  DW1000            5368709120

C. Use DATABASEPROPERTYEX to verify connection to replica

When you use the Azure SQL Database scale-out feature, you can verify whether you're connected to a read-only replica or not by running the following query in the context of your database. It returns READ_ONLY when you're connected to a read-only replica. This way, you can also identify when a query is running on a read-only replica.

SELECT DATABASEPROPERTYEX(DB_NAME(), 'Updateability');

Related content

• ALTER DATABASE (Transact-SQL)
• Database states
• sys.databases (Transact-SQL)
• sys.database_files (Transact-SQL)
• SERVERPROPERTY (Transact-SQL)