Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Retrieving SQL Server Database Properties with DATABASEPROPERTYEX


By:   |   Read Comments (6)   |   Related Tips: More > Database Administration

Attend this free live MSSQLTips webcast

Tips and Tricks to Growing Your SQL Server Estate from SMB to Enterprise Scale
Thursday, June 28, 2018 - click here to learn more


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 2000 and 2005 have 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.  For complete lists look here: SQL 2000, SQL 2005.
 

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

            (Source SQL Server 2000 Books Online)

Next Activity

  • 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


Last Update:


next webcast button


next tip button



About the author
MSSQLTips author Greg Robidoux Greg Robidoux is the President of Edgewood Solutions and a co-founder of MSSQLTips.com.

View all my tips
Related Resources





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Friday, July 15, 2016 - 11:16:29 AM - Greg Robidoux Back To Top

Hi Suman,

you can also query sys.databases and sys.sysdatabases to get more info.

-Greg

 


Friday, July 15, 2016 - 10:36:41 AM - Suman Back To Top

How to get info of advanced options like "Page verify" , recovery interval etc? 

 

 


Tuesday, July 28, 2015 - 7:03:18 PM - David Hutton Back To Top

Thanks!  I know that this information is old but I have to support SQL Server 2000 databases (why me?) and I was looking for this to run some checks on the databases. 

David


Monday, June 02, 2014 - 6:43:16 AM - Akshay Arora Back To Top

what is the query for retrieval or download of a file from MS SQL server Database , If the files are already inserted into that database & now I have to download it 


Friday, March 23, 2012 - 9:44:44 AM - Greg Robidoux Back To Top

You can get a lot of this information from this query:

select * from sys.databases


Friday, March 23, 2012 - 8:40:24 AM - Henry Tu Back To Top

Where does SQL Server store database properties?  Is there a system table or view where I can get database properties without using the function DATABASEPROERTYEX?  Is there a stored procedure similar to sp_helptext where I can display definition of a function?  Any information is appriceated.  Thanks!

 

 


Learn more about SQL Server tools