Is it possible to programmatically determine the highest compatibility level a particular SQL Server instance supports, or at least set the level of a database to the default for the instance without knowing in advance what that is?
The motivation here is that I was previously setting all databases to an earlier compatibility level. Now I would like to migrate them to a higher level to benefit from newer features, but I don't want to break things for customers who happen to still be running older SQL Server versions.
ALTER DATABASE will let me choose a specific compatibility level such as 150 but I can't see a way to just set it to go to the default level for the instance, or to determine the highest level supported except possibly through some hack involving parsing the version number (although this doesn't seem particularly reliable).
Unfortunately, there is no built-in way to do this in SQL Server. SSMS and most other tools indeed just parse the version information.
Here is a horrible hack to get the information out of the error message that results from providing an incorrect comaptibility level.
BEGIN TRY
EXEC('
ALTER DATABASE master
SET COMPATIBILITY_LEVEL = 0;
');
END TRY
BEGIN CATCH
DECLARE @msg nvarchar(4000) = ERROR_MESSAGE();
SELECT SUBSTRING(@msg, LEN(@msg) - 3, 3);
END CATCH;
For reference, the message is something like this, with the last value being the highest.
Valid values of the database compatibility level are 100, 110, 120, 130, 140, 150 or 160.