While writing article about database collation, I came across sys.databases and DATABASEPROPERTYEX. It was very interesting to me that this two can tell user so much about database properties.
Following are main database status: (Reference: BOL Database Status)
Database is available for access.
Database is unavailable.
One or more files of the primary filegroup are being restored, or one or more secondary files are being restored offline.
Database is being recovered.
SQL Server has encountered a resource-related error during recovery.
At least the primary filegroup is suspect and may be damaged.
User has changed the database and set the status to EMERGENCY.
Let us see how we can find out database status using this sys.databases and DATABASEPROPERTYEX.
1) Using T-SQL (My Recommendation)
SELECT DATABASEPROPERTYEX('AdventureWorks', 'Status')
SELECT state_desc DatabaseStatus_sysDatabase
WHERE name = 'AdventureWorks'
2) Using SQL Server Management Studio