What is the best query to use to monitor a SQL Server database's status?

If you're using SQL 2005+ and only want to return the DB name where the DB isn't in the "ONLINE" state I'd use this:

SELECT
    name
FROM sys.databases
WHERE state != 0;

Remember that databases participating in mirroring or log shipping will not be online or may change state regularly. For more info about the sys.databases DMV see documentation here: http://msdn.microsoft.com/en-us/library/ms178534.aspx

I'd use the newer sys.databases not sydatabases but otherwise this is OK

Not least, you don't need DATABASEPROPERTY calls

SELECT
   name, state_desc
FROM
   sys.databases
WHERE
   state IN (4, 5, 6)

the way that I found to see the db status is to use the function DATABASEPROPERTYEX ( database , property ), like this:

SELECT DATABASEPROPERTYEX('AdventureWorks', 'Status').

The statuses are pretty self explanatory:

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 did not recover.

EMERGENCY = Database is in an emergency, read-only state. Access is restricted to sysadmin members

In Ola Hallengren's blog (an SQL MVP), in his tool to verify the database integrity, I found he's using the view sys.database_recovery_status to query a db status. If the db has a row in this view, then it's live and kicking, if not, it's offline.

PS: the databaseproperty function that you use is going to be removed in future versions, so databasepropertyex is replacing it.