Rajendra Gupta
Script output

Explore the DATABASEPROPERTYEX() function for SQL Server databases

May 28, 2020 by

In this article, we explore a SQL function to extract SQL database metadata using the DATABASEPROPERTYEX function.

Introduction

We might want to retrieve the database property value. You can connect to SQL Instance using SSMS and get these configurations from the property window. You might not want to use GUI in case you need to check the property for multiple databases. It is possible but time-consuming. You cannot export the data in an excel format as well, and it again causes issues to write down values manually:

SQL database properties

SQL Server provides built-in function DATABASEPROPERTY and DATABASEPROPERTYEX() to retrieve the information. It is essential to know the difference in these functions before proceeding further.

SQL Server 2012 introduced a new metadata function DATABASEPROPERTYEX. An earlier version of it, DATABASEPROPERTY, is still available but mainly for the backward compatibility. The main issue with DATABASEPROPERTY is that it returns only INT data type in the output. We get sql_variant data type output in the new function DATABASEPROPERTYEX.

Once we write this function in SSMS, you get information about its argument and return values.

function syntax

It requires two arguments:

  • Database Name: Specify the database name for which we want to retrieve the information
  • Property Name: Specify the property name to get its configured value in the database specified in the first argument

Let’s explore this built-in function using examples.

Example 1: Get database collation

SQL Server collation is a set of characters and encoding rules. You might have a specific collation for a database depending upon the requirement.

Argument: Collation

Get database collation

In this case, the [AdventureWorks] database has a collation SQL_Latin1_General_CP1_CI_AS.

If we do not specify a database name in the first argument, it does not give any error message. You get NULL in the output.

Get database collation for blank database

Example 2: Check database status using DATABASEPROPERTYEX() function

We can have different statuses of a SQL database, as specified below:

  • Online: It shows that the database is available for queries
  • Offline: Sometimes, we do not want database available for users, but it should exist in the SQL instance. In this case, we can set the database to offline status
  • Restoring: We can see restoring status if a database is being restored from the backup, or we have not performed the recovery on a database
  • Suspect: In case of any issues with the database such as Primary or secondary data file corruption, you can see the database in the suspect mode
  • Recovering: Once the database is performing a recovery process, you can see the database in recovery status. You might see this message in case you restart SQL Server while any active transactions are running

You can go through an article Understanding different SQL Server database states for more details.

Argument: Status

As shown here, the database [AdventureWorks] database is in ONLINE status.

Check database status using DATABASEPROPERTYEX() function

Example 3: Check database user access using DATABASEPROPERTYEX() function

Usually, a database remains in a multi-user mode. In this mode, many users can connect to the database and execute queries.

We can also set a database in a single user mode. In this mode, the only user with db_owner, dbcreator, or sysadmin can connect to a database, but only a single database connection is allowed.

Apart from these modes, we can set a database in the RESTRICTED_USER mode. In this mode, only users with db_owner, dbcreator, and sysadmin roles can connect to the database.

Argument: UserAccess

We have a database [AdventureWorks] in the multi-user mode.

Check database user access using DATABASEPROPERTYEX() function

Example 4: Check Auto Close property status of a SQL database using DATABASEPROPERTYEX() function

We can set a database to close automatically using the AUTO_CLOSE property set to true. In this case, the database shuts down and frees system resources once the last user exits.

For this example, let’s turn on AUTO_CLOSE property on my test database [Showroom] using the below script.

Now, we check the value for AUTO_CLOSE using the argument IsAutoClose in the [Showroom] and [AdventureWorks] databases.

In the below screenshot, we verify that the [Showroom] database is set to auto close whereas [AdventureWorks] database does not have this property enabled:

Check Auto Close property status of a database using DATABASEPROPERTYEX() function

Example 5: Check Auto-Create and Auto-update Statistics property status of a SQL database using DATABASEPROPERTYEX() function

SQL Server query optimizer creates the statistics automatically based on the query predicates. We can see statistics starting from _WA_sys name.

Argument: IsAutoCreateStatistics

Auto create statistics

Argument: IsAutoUpdateStatistics

We can control the query optimizer behavior to update SQL Server statistics automatically based on a predefined threshold. You should always set this property enabled unless you have a specific reason to disable auto-update statistics.

In the output, value 1 shows that auto-update statistic is turned on for [Showroom] database:

Check Auto Create and Auto update Statistics property status of a database using DATABASEPROPERTYEX() function

Example 6: Check whether the database is a schema and statistics only copy of a SQL database

SQL Server 2014 SP2 onwards, we can create a blank database with the schema-and statistics-only copy of a user database using DBCC CLONEDATABASE. We can use DATABASEPROPERTYEX to check whether it is a clone database or not.

Check whether database is a schema and statistics only copy

In an earlier example, we set [Showroom] database to auto-close once the last user exits. If we check the clone property for this database, it returns NULL output because It cannot access the database to check its property.

Argument: IsClone

IsClone argument

Example 7: Check last date and time of the consistency check for a SQL database

We should perform regular consistency checks of all databases in SQL instance using DBCC CHECKDB. It ensures the database is consistent and does not have any corruption. We can check the timestamp of the last successful database consistency check.

Argument: LastGoodCheckDbTime

Check last date and time of the consistency check for a database

In my demo environment, I do not have performed DBCC CHECKDB on the master database. If we have not performed a DBCC CHECKDB on a database, it returns default output 1900-01-01 00:00:00.000

Default output

Example 8: Check database recovery model for a SQL database

SQL Server provides Full, Bulk-logged, and Simple recovery model. It determines the kind of database backups and the ability to recover data in case of any issues.

Argument: Recovery

In the below query, we check the recovery model for three databases – Master, SQLShack & Javatest. We see different recovery model set for all databases in a SQL instance.

You can refer to Understanding SQL Server database recovery models to explore recovery models.

Check database recovery model for a database

Example 9: Get values for different properties of all SQL databases using DATABASEPROPERTYEX function

We can go through the Microsoft docs for all arguments supported by the DATABASEPROPERTYEX function. In this example, we want to retrieve the configurations for all SQL databases available in the SQL instance. I found a useful script in Microsoft TechNet for it. I modified this script to include SQL Server 2019 version as well and attached to this article.

This script uses a Case statement in SQL Server. You can refer to the article CASE statement in SQL to learn it. It also gets the database name for the first argument using the sys.databases system view:

Download the DATABASEPROPERTYEX.sql file here

In the output, we can see different properties returned for all SQL databases using the DATABASEPROPERTYEX function:

Script output

Conclusion

In this article, we explored useful DATABASEPROPERTYEX function to check configuration values for SQL databases. It comes as a handy and useful script where you need to check values for all SQL databases available in the instance. We cannot use the GUI method in this case, as it would take a longer time. You can use the DATABASEPROPERTYEX function to check the values, export it to CSV, Excel format, and store it for your records.

Rajendra Gupta
Latest posts by Rajendra Gupta (see all)

Related posts:

  1. A complete guide to T-SQL Metadata Functions in SQL Server
  2. SQL Server Statistics and how to perform Update Statistics in SQL
  3. Top SQL Server Books
  4. Accelerated Database Recovery; Instant Rollback and Database Recovery
  5. SQL Server Statistics in Always On Availability Groups
168 Views