SQL Server offers various metadata functions to return metadata information about databases and its objects. Today, I will discuss one of the popular system functions using which we can fetch database properties for a specified database. This article will explain the metadata function, DATABASEPROPERTYEX(), and its use cases.
The syntax to use this function is given in the below statement. You must specify the name of the database as the database argument and the name of the property from the below list as the property argument to return its result.
SELECT DATABASEPROPERTYEX ( database , property )
The list of all properties which can be returned using this metadata function is given in the below bullets.
- 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
The number of properties given above is too big, so we cannot showcase the use case of each property in this article, but we will learn how to use this function with the help of its examples and use cases in the below section.
Use Cases
Let me show you an example using which you can get the existing configurable properties of a specified database's auto close, auto shrink, and auto-update statistics. Let’s first check these database properties using the GUI by accessing the database properties window, and then we will return the result set of these properties using the metadata function DATABASEPROPERTYEX. You can use SQL Server Management Studio or SQL Manager to execute all the queries given in this article.
I accessed the database properties window of a user database, testdb1, and then clicked the Options tab from the left side pane to see all the details in its right-side pane. We can see all these settings in the below image which is highlighted in yellow. Have a look at each configured setting.
Now, let’s get the same result using this function DATABASEPROPERTYEX().
This metadata function will return the same information about the above settings by specifying the below properties arguments.
- IsAutoShrink tells whether database files are set to automatic shrink operation. This will return 1 if auto shrink is enabled or set to true and 0 if auto shrink is not enabled and set to false.
- IsAutoUpdateStatistics defines whether auto update statistics is set to true or false. If you get its output as 1, it is set to true; if the output is 0, it means it is set to false.
- IsAutoClose will tell whether the auto-close setting is set to true or false. If you get its output as 1, it means it is set to true; if the output is 0, it means it is set to false.
- IsAutoCreateStatistics will let us know whether auto-create statistics is set to true or false. If you get its output as 1, it is set to true; if the output is 0, it means it is set to false.
- IsAutoCreateStatisticsIncreament tells whether this setting is set to true or false. If you get its output as 1, it is set to true; if the output is 0, it means it is set to false.
We will specify a database name along with the above properties to return the respective output. You can see below the query in which all properties were specified for a user database testdb1. Let’s run it and get its result.
SELECT DATABASEPROPERTYEX('testdb1', 'IsAutoShrink') AS [testdb1 Auto Shrink],
DATABASEPROPERTYEX('testdb1', 'IsAutoUpdateStatistics') AS [testdb1 Auto stats],
DATABASEPROPERTYEX('testdb1', 'IsAutoClose') AS [testdb1 Auto Close],
DATABASEPROPERTYEX('testdb1', 'IsAutoCreateStatistics') AS [testdb1 Auto Create Stats],
DATABASEPROPERTYEX('testdb1', 'IsAutoCreateStatisticsIncremental') AS [testdb1 Auto Create StatsIncremental]
GoHere, we can see the output of the above statement which shows:
- Auto shrink property has returned output as 0 which means auto shrink setting is set to false for database testdb1
- Auto update stats has returned as 1 which means this setting is enabled and set to true
- Auto close is set to false as it has returned 0
- Auto create stats is set to true because its output is showing as 1
- Auto create stats incremental is also disabled and set to false
You can compare the output of the query to the settings shown in the database properties window showing in the first image of this article.
We can also get a similar output by specifying the other system function DB_NAME() in place of the database name argument. Here, you need to select the target database context for which you want to fetch these details. Either you can select the database from the dropdown or add a USE statement to switch to the desired database context.
I have used the DB_NAME() system function in the below query, selecting our target database testdb1 using the USE statement. Let’s run it and get the output.
Use testdb1
GO
SELECT DATABASEPROPERTYEX(DB_NAME(), 'IsAutoShrink') AS [testdb1 Auto Shrink],
DATABASEPROPERTYEX(DB_NAME(), 'IsAutoUpdateStatistics') AS [testdb1 Auto stats],
DATABASEPROPERTYEX(DB_NAME(), 'IsAutoClose') AS [testdb1 Auto Close],
DATABASEPROPERTYEX(DB_NAME(), 'IsAutoCreateStatistics') AS [testdb1 Auto Create Stats],
DATABASEPROPERTYEX(DB_NAME(), 'IsAutoCreateStatisticsIncremental') AS [testdb1 Auto Create StatsIncremental]The output of the above query has been returned in the below image which shows the same output as it was returned in the above example.
The above examples show how to get the result set of various database properties for a specified database. Suppose you want to display a similar result for all your databases hosted on a SQL Server instance, then you need to use this metadata function along with columns and tables.
I have specified a column name of system object sysdatabases in which database names are stored as a first argument where we have specified the database name in the above examples. Look at the below query where I call each database stored in the specified column from a system table sysdatabases to display their result set. We can also use the system table sys.databases system table in the below example.
SELECT name,
DATABASEPROPERTYEX(name, 'IsAutoShrink') AS [DB Auto Shrink],
DATABASEPROPERTYEX(name, 'IsAutoUpdateStatistics') AS [DB Auto stats],
DATABASEPROPERTYEX(name, 'IsAutoClose') AS [DB Auto Close],
DATABASEPROPERTYEX(name, 'IsAutoCreateStatistics') AS [DB Auto Create Stats],
DATABASEPROPERTYEX(name, 'IsAutoCreateStatisticsIncremental') AS [DB Auto Create StatsIncremental]
FROM sysdatabasesThe result of the above query is shown in the below image, where we can see the current configuration for each property for every database.
Let me take another example: I will take another database property to get the database Status and its User Access ability. The details about both properties are given below:
- Status will return the current state of the database whether it is ONLINE for user requests, OFFLINE, RESTORING, RECOVERING, SUSPECT, or in the EMERGENCY state.
- UserAccess will display whether database accessibility is in SINGLE_USER, RESTRICTED_USER or in the MULTI_USER for all requests.
I have specified user database testdb1 in the below example along with DB_NAME() system function in another example to return the database status and its user accessibility.
SELECT DATABASEPROPERTYEX('testdb1', 'Status') AS [testdb1 Status],
DATABASEPROPERTYEX('testdb1', 'UserAccess') AS [testdb1 User access]
Go
Use testdb1
GO
SELECT DATABASEPROPERTYEX(DB_NAME(), 'Status') AS [testdb1 Status],
DATABASEPROPERTYEX(DB_NAME(), 'UserAccess') AS [testdb1 User access]The output of the above query shows that database testdb1 is ONLINE and set to multi-user in the below screen.
Another use case in which I have returned another database property Version and updateability of a specified database.
- Version tells about the Internal version number of the SQL Server code with which the database was created. If we get any version number in output, it means the database is open if you get NULL it means the database is not open for user access.
- Updateability tells whether the database can be modified or not. You might get any of the 2 values for this property, READ_ONLY or READ_WRITE. If the database is set for read-only operations, its output will return READ_ONLY, and if the database is set for read-write both, you will get READ_WRITE.
Here is the query which will be run to get the output of the above properties for a database testdb1. I have kept the other 2 properties as well in this example which I explained in the last use case.
SELECT DATABASEPROPERTYEX('testdb1', 'Version') AS [testdb1 Version],
DATABASEPROPERTYEX('testdb1', 'Status') AS [testdb1 Status],
DATABASEPROPERTYEX('testdb1', 'UserAccess') AS [testdb1 User access],
DATABASEPROPERTYEX('testdb1', 'Updateability') AS [testdb1 Updateability]
GoYou can see its output in the below image.
You can also get a similar output by using the below query.
Use testdb1 GO SELECT DATABASEPROPERTYEX(DB_NAME(), 'Version') AS [testdb1 Version], DATABASEPROPERTYEX(DB_NAME(), 'Status') AS [testdb1 Status], DATABASEPROPERTYEX(DB_NAME(), 'UserAccess') AS [testdb1 User access], DATABASEPROPERTYEX(DB_NAME(), 'Updateability') AS [testdb1 Updateability]
The next use case is about getting database recovery and one of the database feature of full-text searches. We can get this information by accessing the following properties.
- Recovery tells about the configured value of the database recovery model
- IsFulltextEnabled tells about whether the full-text search is enabled or not for the specified database. If you get its output as 1 it means it is set to true and the feature is enabled; if the output is 0, it means it is false.
Here also I used both approaches to show you its output. Initially, I specified the target database name for which I need to fetch the values of these properties and later by using the system function DB_NAME.
SELECT DATABASEPROPERTYEX('testdb1', 'Recovery') AS [testdb1 Version],
DATABASEPROPERTYEX('testdb1', 'IsFulltextEnabled') AS [testdb1 FullText]
GO
Use testdb1
GO
SELECT DATABASEPROPERTYEX(DB_NAME(), 'Recovery') AS [testdb1 Version],
DATABASEPROPERTYEX(DB_NAME(), 'IsFulltextEnabled') AS [testdb1 FullText]
We can see the output of the above query in the below image which shows the recovery of the database is showing as Full whereas the full-text output is showing as 1 which means it is enabled for this database.
SELECT DATABASEPROPERTYEX('testdb1', 'Recovery') AS [testdb1 Version],
DATABASEPROPERTYEX('testdb1', 'IsFulltextEnabled') AS [testdb1 FullText]
GO
Use testdb1
GO
SELECT DATABASEPROPERTYEX(DB_NAME(), 'Recovery') AS [testdb1 Version],
DATABASEPROPERTYEX(DB_NAME(), 'IsFulltextEnabled') AS [testdb1 FullText]
Conclusion
Today, I explained one of the metadata functions DATABASEPROERTYEX in the article along with its various use cases. Using this metadata function, you can try other properties to get the desired result. Please share your feedback in the comment section. Your comments help us improve in a better way.