What's the status of the databases with MSSQL

Applies to:

  • Microsoft SQL Server 2000
  • Microsoft SQL Server 2005

Database statuses

SELECT SUBSTRING(name,1,25) AS 'Database',
       CAST(DATABASEPROPERTYEX(name, 'Status') AS VARCHAR(15)) AS 'Status',
       CAST(DATABASEPROPERTYEX(name, 'Recovery')AS VARCHAR(15)) AS 'Recovery',
       CASE WHEN DATABASEPROPERTYEX(name,'IsAutoClose') = 1 THEN 'Yes'
            WHEN DATABASEPROPERTYEX(name,'IsAutoClose') = 0 THEN 'No'
       END AS 'Auto close',
       CASE WHEN DATABASEPROPERTYEX(name,'IsAutoCreateStatistics') = 1 THEN 'Yes'
            WHEN DATABASEPROPERTYEX(name,'IsAutoCreateStatistics') = 0 THEN 'No'
       END AS 'Update stats',
       CAST(DATABASEPROPERTYEX(name, 'SQLSortOrder')AS VARCHAR(15)) AS 'Sort order',
       CAST(DATABASEPROPERTYEX(name, 'UpdateAbility')AS VARCHAR(15)) AS 'Updates',
       CAST(DATABASEPROPERTYEX(name, 'UserAccess')AS VARCHAR(16)) AS 'Access',
       CAST(DATABASEPROPERTYEX(name, 'Version')AS VARCHAR(12)) AS 'Version'
FROM master.dbo.sysdatabases
ORDER BY name;
go
Database                  Status          Recovery        Auto close Update stats Sort order      Updates         Access           Version
------------------------- --------------- --------------- ---------- ------------ --------------- --------------- ---------------- ------------
adp1SQL                   ONLINE          FULL            No         Yes          52              READ_WRITE      MULTI_USER       611
AdventureWorks            ONLINE          FULL            No         Yes          52              READ_WRITE      MULTI_USER       611
AdventureWorksDW          ONLINE          SIMPLE          No         Yes          52              READ_WRITE      MULTI_USER       611
AdventureWorksLT          ONLINE          SIMPLE          No         Yes          0               READ_WRITE      MULTI_USER       611
master                    ONLINE          SIMPLE          No         Yes          52              READ_WRITE      MULTI_USER       611
model                     ONLINE          FULL            No         Yes          52              READ_WRITE      MULTI_USER       611
msdb                      ONLINE          SIMPLE          No         Yes          52              READ_WRITE      MULTI_USER       611
ReportServer              ONLINE          SIMPLE          No         Yes          0               READ_WRITE      MULTI_USER       611
ReportServerTempDB        ONLINE          SIMPLE          No         Yes          0               READ_WRITE      MULTI_USER       611
sql911                    ONLINE          FULL            Yes        Yes          NULL            READ_WRITE      MULTI_USER       NULL
tempdb                    ONLINE          SIMPLE          No         Yes          52              READ_WRITE      MULTI_USER       611

(11 row(s) affected)
  • The substring and the cast are used to keep the column short enough for the making it readable.
  • The 3 more important properties [according to me] are:
    1. Status: Is it online or not? Is it available to the users?
    2. Recovery: Full recovery means restore to a specific transaction.
    3. IsAutoCreateStatistics: Existing statistics are automatically updated when the data that affect the statistics changes.