SQL SERVER DBA, Linux and Azure: Generate and change all SQL databases to a different compatibility level - Alter SQL Compatibility Level

Generate and change all SQL databases to a different compatibility level - Alter SQL Compatibility Level


SELECT name, compatibility_level  

FROM sys.databases where compatibility_level not in(120) -- SQL Server 2012 compatibility_level is 120

---------------------------------

SELECT name, compatibility_level  

FROM sys.databases 

---------------------------

/* Start Of Code */


DECLARE @SQL VARCHAR(max)  = ''

,             @CompLevel int = 120 -- Need to change the compatibility_level as per SQL Server version


SELECT @SQL += 'ALTER DATABASE ' + quotename(NAME) + ' SET COMPATIBILITY_LEVEL = ' + cast(@CompLevel as char (3)) + ';' + CHAR(10) + CHAR(13)

FROM sys.databases

WHERE 

--COMPATIBILITY_LEVEL not in(120) 

--and 

 compatibility_level <> @CompLevel


PRINT @SQL

--EXEC (@SQL)



/* End Of Code */

No comments:

Post a Comment