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.


The DATABASEPROPERTYEX  function extracts metadata about a database when we need to retrieve the property value on the basis of 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.


The syntax of the DATABASEPROPERTYEX() function is:

DATABASEPROPERTYEX ( database , property )

It takes two arguments.

The first is database that represents the name of the database.

The second is property that 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


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


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



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


DATABASEPROPERTYEX-Function-in SQL-Server1.jpg

Now to find the information of all databases:

SELECT name,


       DATABASEPROPERTYEX(name, 'Status') ,

        DATABASEPROPERTYEX(name, 'isAutoShrink')     

FROM   master.dbo.sysdatabases


DATABASEPROPERTYEX-Function-in SQL-Server2.jpg 

Up Next
    Ebook Download
    View all
    View all