Database Information

The following SQL Query works with Microsoft SQL Server and allows you to easily get some useful detail on all the databases on the currently connected to SQL Server Instance.

SELECT db.name "Database",
    db.create_date "CreatedDate",
    CASE COALESCE(db.compatibility_level, 0)
        WHEN   0 THEN 'Offline'
        WHEN  70 THEN 'SQL Server 7'
        WHEN  80 THEN 'SQL 2000'
        WHEN  90 THEN 'SQL 2005'
        WHEN 100 THEN 'SQL 2008'
        WHEN 110 THEN 'SQL 2012'
        ELSE 'Unknown'
    END "CompatibilityLevel",
    db.collation_name "Collation",
    db.state_desc "State",
    db.recovery_model_desc "RecoveryModel",
    lo.name "Owner",
    SERVERPROPERTY('ComputerNamePhysicalNetBIOS') "Host Name",
    SERVERPROPERTY('MachineName') "Machine Name",
    SERVERPROPERTY('InstanceName ') "SQL Instance",
    CASE SERVERPROPERTY('IsClustered')
        WHEN 0 THEN 'Not Clustered'
        WHEN 1 THEN 'Clustered'
        ELSE 'Unknown'
    END "Clustering",
    CASE SERVERPROPERTY('IsFullTextInstalled')
        WHEN 0 THEN 'No Full Text Search'
        WHEN 1 THEN 'Full Text Search Installed'
        ELSE 'Unknown'
    END "Full Text Searching",
    CASE SERVERPROPERTY('IsIntegratedSecurityOnly')
        WHEN 0 THEN 'SQL and Windows Authentication'
        WHEN 1 THEN 'Windows Authentication'
        ELSE 'Unknown'
    END "Authentication Mode",
    SERVERPROPERTY('Edition') "SQL Server Edition",
    SERVERPROPERTY('ProductVersion') "SQL Server Version",
    SERVERPROPERTY('ProductLevel') "Product Level" --RTM, SP or CTP, not CU
FROM sys.databases db
INNER JOIN sys.syslogins lo ON db.owner_sid = lo.sid