In SQL Server, the DATABASEPROPERTYEX() function returns the current setting of the specified database option or property for the specified database.
For example, you can use it to return the collation of a database, check the level of user access, etc.
It accepts two arguments: the database, and the property you want information about.
Syntax
The syntax goes like this:
DATABASEPROPERTYEX ( database , property )
Example
Here’s an example to demonstrate the basic usage of this function:
SELECT DATABASEPROPERTYEX('Music', 'Collation') AS Result;
Result:
+------------------------------+ | Result | |------------------------------| | SQL_Latin1_General_CP1_CI_AS | +------------------------------+
In this case, the database called Music has a collation of SQL_Latin1_General_CP1_CI_AS.
Here’s another example:
SELECT DATABASEPROPERTYEX('WideWorldImportersDW', 'UserAccess') AS [Result];
Result:
+------------+ | Result | |------------| | MULTI_USER | +------------+
In this case, the WideWorldImportersDW database has multi-user access.
Full List of Options
Here’s a full list of arguments you can pass to DATABASEPROPERTY() at the time of writing:
- Collation
- ComparisonStyle
- Edition
- IsAnsiNullDefault
- IsAnsiNullsEnabled
- IsAnsiPaddingEnabled
- IsAnsiWarningsEnabled
- IsArithmeticAbortEnabled
- IsAutoClose
- IsAutoCreateStatistics
- IsAutoCreateStatisticsIncremental
- IsAutoShrink
- IsAutoUpdateStatistics
- IsClone
- IsCloseCursorsOnCommitEnabled
- IsFulltextEnabled
- IsInStandBy
- IsLocalCursorsDefault
- IsMemoryOptimizedElevateToSnapshotEnabled
- IsMergePublished
- IsNullConcat
- IsNumericRoundAbortEnabled
- IsParameterizationForced
- IsQuotedIdentifiersEnabled
- IsPublished
- IsRecursiveTriggersEnabled
- IsSubscribed
- IsSyncWithBackup
- IsTornPageDetectionEnabled
- IsVerifiedClone
- IsXTPSupported
- LastGoodCheckDbTime
- LCID
- MaxSizeInBytes
- Recovery
- ServiceObjective
- ServiceObjectiveId
- SQLSortOrder
- Status
- Updateability
- UserAccess
- Version
Note that some of these are only applicable to Azure SQL Database and SQL Data Warehouse.
See the Microsoft documentation for a detailed explanation of each property.
Also see OBJECTPROPERTYEX() for a similar function that returns information about schema-scoped objects rather than databases.