Is the version of SQL Server with which the database is to be made compatible. The following compatibility level values can be configured (not all versions supports all of the above listed compatibility level):
The compatibility level of a database dictates how certain language elements of the database functions when it relates to an earlier version of SQL Server.  In a nutshell, this offers up partial “backward compatibility” to an earlier version.

Syntax

ALTER DATABASE database_name SET COMPATIBILITY_LEVEL = { 150 | 140 | 130 | 120 | 110 | 100 | 90 }

 

Product	Database Engine Version	Compatibility Level Designation	Supported Compatibility Level Values
SQL Server 2017 (14.x)	14	140	140, 130, 120, 110, 100
Azure SQL Database logical server	12	130	150, 140, 130, 120, 110, 100
Azure SQL Database Managed Instance	12	130	150, 140, 130, 120, 110, 100
SQL Server 2016 (13.x)	13	130	130, 120, 110, 100
SQL Server 2014 (12.x)	12	120	120, 110, 100
SQL Server 2012 (11.x)	11	110	110, 100, 90
SQL Server 2008 R2	10.5	100	100, 90, 80
SQL Server 2008	10	100	100, 90, 80
SQL Server 2005	9	90	90, 80
SQL Server 2000	8	80	80

 

Query the existing Compatibility of the databases on SQL server:

SELECT Name, compatibility_level
FROM sys.databases

Query the existing databases which are set compatible with current database or in other words set to be compatible with previous version on the SQL server:

SELECT Name, compatibility_level
FROM sys.databases where COMPATIBILITY_LEVEL != ‘130’

Setting all databases to a desired COMPATIBILITY_LEVEL :

use master Declare @databaseName NVARCHAR(MAX) Declare @Query NVARCHAR(MAX) Declare @GetDBName Cursor set @GetDBName = CURSOR for select name FROM sys.databases where COMPATIBILITY_LEVEL != ‘130’ and name NOT IN (‘model’,’tempdb’,’master’,’msdb’) open @GetDBName fetch Next from @GetDBName into @databaseName while @@FETCH_STATUS = 0 begin Set @Query = ‘ALTER DATABASE ‘ + @databaseName + ‘ SET ‘+ ‘COMPATIBILITY_LEVEL =130’ –Print @Query EXEC sys.sp_executesql @Query fetch next from @GetDBName into @databaseName end close @GetDBName Deallocate @GetDBName

 

If you liked this post, say thanks by sharing it.