Quick database information

Sometimes you need just the basic info about a database. This quit bit, is just that.

Here you will get the basic information such as Edition, Compatability Level etc.

script

SELECT 
    SERVERPROPERTY('EngineEdition') AS EngineEdition,
    CASE SERVERPROPERTY('EngineEdition')
        WHEN 5 THEN 'Azure SQL Database'
        WHEN 8 THEN 'Azure SQL Managed Instance'
        ELSE 'SQL Server'
    END AS PlatformType,
    SERVERPROPERTY('ProductVersion') AS ProductVersion,
    SERVERPROPERTY('ProductLevel') AS ProductLevel,
    SERVERPROPERTY('Edition') AS Edition,
    DB_NAME() AS DatabaseName,
    d.compatibility_level AS CompatibilityLevel,
    d.collation_name AS Collation,
    d.state_desc AS DatabaseState,
    d.recovery_model_desc AS RecoveryModel,
    CAST(SUM(df.size) * 8.0 / 1024 AS DECIMAL(10,2)) AS AllocatedSizeMB,
    CAST(SUM(CAST(FILEPROPERTY(df.name, 'SpaceUsed') AS BIGINT)) * 8.0 / 1024 AS DECIMAL(10,2)) AS UsedSpaceMB,
    CAST((SUM(df.size) - SUM(CAST(FILEPROPERTY(df.name, 'SpaceUsed') AS BIGINT))) * 8.0 / 1024 AS DECIMAL(10,2)) AS FreeSpaceMB
FROM sys.databases d
CROSS APPLY sys.database_files df
WHERE d.name = DB_NAME()
GROUP BY d.name, d.compatibility_level, d.collation_name, d.state_desc, d.recovery_model_desc;