DATABASEPROPERTYEX Function in SQL Server

Introduction

In this article, I would like to show how to extract the metadata about a database in SQL Server. Here you will see two examples, one to extract the information of a specific database and the other to find the information of all databases. So let's have a look at a practical example of how to use the DATABASEPROPERTYEX function in SQL Server.

DATABASEPROPERTYEX Function in SQL Server

The DATABASEPROPERTYEX  function extracts metadata about a database when we need to retrieve the property value on the basis of the property and database name. SQL Server has a built-in function called DATABASEPROPERTYEX that allows you to return the specific information of a database and all databases.

Syntax 

The syntax of the DATABASEPROPERTYEX() function is:

DATABASEPROPERTYEX ( database , property )

It takes two arguments.

1. The first is a database that represents the name of the database.
2. The second is property which represents the name of the database property to return.

The following are some of the possible property names.

Status Property- This property value returns the database status. It contains the following return values:

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

UserAccess- This property value indicates which users can access the database. It contains the following return values:

• SINGLE_USER only onedb_owner, dbcreator, orsysadmin user at a time
• RESTRICTED_USER- only members of db_owner,dbcreator, and sysadminroles
• MULTI_USER- all users

IsAutoShrink- Database files are candidates for automatic periodic shrinking. It contains the following return values:

• 1 = TRUE
• 0 = FALSE
• NULL = Invalid Input

Return Type

The return type of the DATABASEPROPERTYEX function is a sql_variant.

Example (Specific information of database)

To find the specific information of a database.

SELECT DATABASEPROPERTYEX('Registration', 'UserAccess')as UserAccess
go

SELECT DATABASEPROPERTYEX('Registration', 'status')as Status
Go

SELECT DATABASEPROPERTYEX('Registration', 'isAutoShrink')as isAutoShrink

Output

Example (Return information to all databases)

To find the database names

If you want a list of all databases then just execute the following query in SQL Server Management Studio.

SELECT name     
FROM   master.dbo.sysdatabases

Output

Now to find the information of all databases.

SELECT name,

DATABASEPROPERTYEX(name, 'UserAccess'),
       DATABASEPROPERTYEX(name, 'Status') ,
       DATABASEPROPERTYEX(name, 'isAutoShrink')     
FROM   master.dbo.sysdatabases

Output

Conclusion

This article will be taught us how to extract the metadata about a database with code examples in SQL Server.