Retrieving SQL Server Database Properties with DATABASEPROPERTYEX
By: Greg Robidoux | Updated: 2023-04-01 | Comments (6) | Related: More > Database Administration
Problem
Finding out information about database settings is not that hard when you use Enterprise Manager or Management Studio, T-SQL commands such as sp_helpdb or query the system tables directly. But the way the information is returned may not always be the most useful. When using the GUI tools you can't easily grab just the information that you need and you can only look at one database at a time. With the T-SQL commands such as sp_helpdb the database options are all strung together and not listed out individually. And when querying the system tables, you often need to join multiple tables to get the results you need as well as convert the results to make sense of them.
Solution
SQL Server has a built-in function called DATABASEPROPERTYEX that allows you to return the specific information you are looking for, for one or all databases. This function can be called from a SELECT statement to return the results of one or more databases. So to find out the recovery model and status for all databases on my server I can run the following command and the get following results:
SELECT name, DATABASEPROPERTYEX(name, 'Recovery'), DATABASEPROPERTYEX(name, 'Status') FROM master.dbo.sysdatabases ORDER BY 1
| Database | RecoveryModel | Status |
|---|---|---|
| AdventureWorks2000 | FULL | ONLINE |
| DB_HISTORY | FULL | ONLINE |
| edgecrm | FULL | ONLINE |
| esarticles | FULL | ONLINE |
| master | SIMPLE | ONLINE |
| model | FULL | ONLINE |
| msdb | SIMPLE | ONLINE |
| mssqltipdb | SIMPLE | ONLINE |
| Northwind | FULL | ONLINE |
| prdTime21 | FULL | ONLINE |
| pubs | SIMPLE | ONLINE |
| PW | FULL | ONLINE |
| ReportServer | FULL | ONLINE |
| ReportServerTempDB | SIMPLE | ONLINE |
| tempdb | SIMPLE | ONLINE |
Some of the things you can find out about your databases include the following information.
| Value | Description | Value returned |
|---|---|---|
| IsAutoClose | Database shuts down cleanly and frees resources after the last user exits. | 1 = TRUE 0 = FALSE NULL = Invalid input |
| IsAutoCreateStatistics | Existing statistics are automatically updated when the statistics become out-of-date because the data in the tables has changed. | 1 = TRUE 0 = FALSE NULL = Invalid input |
| IsAutoShrink | Database files are candidates for automatic periodic shrinking. | 1 = TRUE 0 = FALSE NULL = Invalid input |
| IsAutoUpdateStatistics | Auto update statistics database option is enabled. | 1 = TRUE 0 = FALSE NULL = Invalid input |
| IsPublished | The tables of the database can be published for snapshot or transactional replication, if replication is installed. | 1 = TRUE 0 = FALSE NULL = Invalid input |
| IsSubscribed | Database can be subscribed for publication. | 1 = TRUE 0 = FALSE NULL = Invalid input |
| IsTornPageDetectionEnabled | Microsoft® SQL Server™ detects incomplete I/O operations caused by power failures or other system outages. | 1 = TRUE 0 = FALSE NULL = Invalid input |
| Recovery | Recovery model for the database. | FULL = full recovery model BULK_LOGGED = bulk logged model SIMPLE = simple recovery model |
| Status | Database status. | ONLINE = database is available for query OFFLINE = database was explicitly taken offline RESTORING = database is being restored RECOVERING = database is recovering and not yet ready for queries SUSPECT = database cannot be recovered |
| Updateability | Indicates whether data can be modified. | READ_ONLY = data can be read but not
modified READ_WRITE = data can be read and modified |
| UserAccess | Indicates which users can access the database. | SINGLE_USER = only one db_owner,
dbcreator, or sysadmin user at a time RESTRICTED_USER = only members of db_owner, dbcreator, and sysadmin roles MULTI_USER = all users |
| Version | Internal version number of the Microsoft SQL Server code with which the database was created. For internal use only by SQL Server tools and in upgrade processing. | Version number = Database is open NULL = Database is closed |
Next Steps
- Take a look at this built-in database function and how you can use it to document your servers or easily find out the settings across all of your databases
- Use this function to audit your servers to see what has changed
About the author
Greg Robidoux is the President and founder of Edgewood Solutions, a technology services company delivering services and solutions for Microsoft SQL Server. He is also one of the co-founders of MSSQLTips.com. Greg has been working with SQL Server since 1999, has authored numerous database-related articles, and delivered several presentations related to SQL Server. Before SQL Server, he worked on many data platforms such as DB2, Oracle, Sybase, and Informix.
View all my tips
Article Last Updated: 2023-04-01