Check Database status as online/offline-CodePudding

I want to check a database status, I will be connecting to SQL Server database using pyodbc driver and would like to pass the database name as parameter and see the response as online, offline? Any help would be appreciated?

MS online documentation says they reveal database status - in the sysdatabases table. But I checked and it does not has any column.

CodePudding user response：

sys.sysdatabases has a column name as status (and new SQL server version, I think column is STATE). where status is displayed in numeric value,

You can use below query, it does not cover all, but some important database activity flag's. Notice the Where clause below you can pass the databasename as a parameter there.

SELECT status as status_id,
    CASE 
    WHEN status = 1 THEN 'New'
    WHEN status = 32 THEN 'loading' 
    WHEN status = 64 THEN 'pre recovery' 
    WHEN status = 128 THEN 'recovering' 
    WHEN status = 256 THEN 'not recovered' 
    WHEN status = 512 THEN 'offline' 
    WHEN status = 1024 THEN 'read only' 
    WHEN status = 2048 THEN 'dbo use only' 
    WHEN status = 32768 THEN 'emergency mode' 
    WHEN status = 65536 THEN 'online' 
    WHEN status = 4194304 THEN 'autoshrink' 
    WHEN status = 1073741824 THEN 'cleanly shutdown' 
    ELSE 'None Provided' END AS 'Status_Values'
FROM sys.sysdatabases WHERE name = 'ENTER_DATABASE_IN_QUESTION_HERE'