Tuesday, 24 June 2014

Check your databases are in the correct version

DECLARE @ver nvarchar(128)
SET @ver = CAST(serverproperty('ProductVersion') AS nvarchar)
SET @ver = SUBSTRING(@ver, 1, CHARINDEX('.', @ver) - 1)

SELECT
[name] AS DatabaseName ,
200 AS Priority ,
'Performance' AS FindingsGroup ,
'Old Compatibility Level' AS Finding ,

( 'Database ' + [name]
 + ' is compatibility level '
 + CAST(compatibility_level AS VARCHAR(20))






 + ', which may cause unwanted results when trying to run queries that have newer T-SQL features.' ) AS Details
FROM    sys.databases


where
   CASE compatibility_level
    WHEN 65  THEN '6'
    WHEN 70  THEN '7'
    WHEN 80  THEN '8'
    WHEN 90  THEN '9'
    WHEN 100 THEN '10'
    WHEN 110 THEN '11'
    WHEN 120 THEN '12'
    end

    <>   @ver