/**
SQL Server Configuration Check
Joe Scott - joe@thescott.net
**/

SET NOCOUNT ON;

/**
SQL Server Instance Information
**/

USE [Master]

PRINT('')

SELECT
    CASE
        WHEN CONVERT(nvarchar(128), SERVERPROPERTY('PRODUCTVERSION')) LIKE '12%' THEN 'SQL Server 2014'
        WHEN CONVERT(nvarchar(128), SERVERPROPERTY('PRODUCTVERSION')) LIKE '13%' THEN 'SQL Server 2016'
        WHEN CONVERT(nvarchar(128), SERVERPROPERTY('PRODUCTVERSION')) LIKE '14%' THEN 'SQL Server 2017'
        WHEN CONVERT(nvarchar(128), SERVERPROPERTY('PRODUCTVERSION')) LIKE '15%' THEN 'SQL Server 2019'
        WHEN CONVERT(nvarchar(128), SERVERPROPERTY('PRODUCTVERSION')) LIKE '16%' THEN 'SQL Server 2022'
        ELSE 'Unsupported'
    END AS 'SQL Server'
    ,SERVERPROPERTY('PRODUCTLEVEL') AS 'Service Pack'
    ,SERVERPROPERTY('PRODUCTVERSION') AS 'Version'
    ,SERVERPROPERTY('EDITION') AS 'Edition'

/**
Instance Configuration
**/

USE [Master]

PRINT('Performing SQL Server Configuration Check')

SELECT
        @@SERVERNAME AS 'Instance'
        ,CASE
           WHEN (SELECT value FROM sys.configurations WHERE configuration_id = 1543) = 0 THEN 'NOT SET'
           ELSE (SELECT value FROM sys.configurations WHERE configuration_id = 1543)
        END AS 'Min Server Memory (MB)'
        ,CASE 
            WHEN (SELECT value FROM sys.configurations WHERE configuration_id = 1544) = 2147483647 THEN 'MAXIMUM'
            ELSE (SELECT value FROM sys.configurations WHERE configuration_id = 1544)
        END AS 'Max Server Memory (MB)'           
        ,CASE 
            WHEN (SELECT value FROM sys.configurations WHERE configuration_id = 1581) = 1 THEN 'ON'
            ELSE 'OFF'
        END AS 'Optimise for Ad-Hoc Workloads'
        ,CASE 
            WHEN (SELECT value FROM sys.configurations WHERE configuration_id = 1538) = 5 THEN 'DEFAULT'
            ELSE (SELECT value FROM sys.configurations WHERE configuration_id = 1538) 
        END AS 'Cost Threshold for Parallelism'
        ,CASE
            WHEN (SELECT is_disabled FROM sys.server_principals WHERE name ='sa' AND principal_id = 1) = 1 THEN 'DISABLED'
            ELSE 'ENABLED'
        END AS 'SQL sa Account';

/**
Database Level
**/

PRINT('Performing Disk Space Check')

SELECT DISTINCT
	vol.logical_volume_name AS 'Volume Name'
	,vol.volume_mount_point AS 'Letter'
	,CONVERT(DECIMAL(18,0), vol.total_bytes/1073741824.0) AS [Volume Size (GB)]
	,CONVERT(DECIMAL(18,0), vol.available_bytes/1073741824.0) AS [Free Space (GB)]
	,CONVERT(DECIMAL(18,2), vol.available_bytes * 1. / vol.total_bytes * 100.) as [Free Space %]
	,CASE
		WHEN CONVERT(DECIMAL(18,2), vol.available_bytes * 1. / vol.total_bytes * 100.) < 20 THEN 'CRITITCAL'
		WHEN CONVERT(DECIMAL(18,2), vol.available_bytes * 1. / vol.total_bytes * 100.) < 50 THEN 'WARNING'
	ELSE 'OK'
	END AS 'Free Space Status'

FROM sys.master_files AS db
CROSS APPLY sys.dm_os_volume_stats([db].[database_id],[db].[file_id]) AS vol
ORDER BY vol.volume_mount_point;

PRINT('Performing Database Checks')

USE [Master]

DECLARE @compatibility_level int

SELECT @compatibility_level = (SELECT compatibility_level FROM sys.databases WHERE name = 'master')

PRINT('Performing Database Checks')
SELECT 
        DB.name AS 'Name'
        ,DB.recovery_model_desc as 'Recovery Model'
        ,CASE 
            WHEN DB.compatibility_level = @compatibility_level THEN 'OK'
            ELSE 'LOWER THAN SERVER'
        END AS 'Compatibility Level'
        ,CASE
			WHEN DB.is_query_store_on = 1 THEN 'ON'
			ELSE 'OFF'
		END AS 'Query Store'
        ,DB.page_verify_option_desc as 'Page Verify'
        ,CASE 
            WHEN DB.owner_sid = 0x01 THEN 'sa'
			ELSE 'USER'
		END AS 'DB Owner'
        ,CASE
            WHEN DB.is_auto_close_on = 0 THEN 'OFF'
            ELSE 'ON'
        END AS 'DB Auto Close'
        ,CASE
            WHEN DB.is_auto_shrink_on= 0 THEN 'OFF'
            ELSE 'ON'
        END AS 'DB Auto Shrink'
        ,DBR.physical_name as 'MDF Name'
        ,DBL.physical_name as 'LDF Name'
FROM sys.databases as DB
LEFT JOIN sys.master_files AS DBR ON DB.database_id = DBR.database_id AND DBR.type = 0
LEFT JOIN sys.master_files AS DBL ON DB.database_id = DBL.database_id AND DBL.type = 1
WHERE DB.Name LIKE 'Example'
ORDER BY DB.Name ASC;