How to change Database Compatibility level in all the databases for a given SQL Server instance?

On 30 Apr, 2015

MS SQL DBA

By : Charith Silva

No Comments

Presume there are several older versions of SQL Server databases exist in your current SQL Server instance and you want to change all of those Databases Compatibility level to higher version at once. Below script might be helpfully you to achieve that goal. This can be set using SQL Server Management Studio but if you have lots of databases it might take very long time.

Here is a listing of the available compatibility levels.

Version	Compatibility Level	Oldest Available Level
SQL 2014	120	100
SQL 2012	110	90
SQL 2008	100	80
SQL 2005	90	70
SQL 2000	80	70
SQL 7.0	70	–

USE [master];
GO

SET NOCOUNT ON
DECLARE @SQL NVARCHAR(MAX);
DECLARE @COMPATIBILITY_LEVEL INT;
SET @COMPATIBILITY_LEVEL=110;----**SET appropriate database compatibility level**


DECLARE #databases CURSOR FOR
SELECT NAME FROM master..sysdatabases where dbid>4

DECLARE @database_name varchar(128)

OPEN #databases
FETCH #databases INTO @database_name 
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @SQL = 'ALTER DATABASE ['+ @database_name+'] SET COMPATIBILITY_LEVEL = '+CAST(@COMPATIBILITY_LEVEL as VARCHAR(10))+'';

EXEC(@SQL)
FETCH #databases INTO @database_name
END

CLOSE #databases
DEALLOCATE #databases

USE [master];

SET NOCOUNT ON

DECLARE @SQL NVARCHAR(MAX);

DECLARE @COMPATIBILITY_LEVEL INT;

SET @COMPATIBILITY_LEVEL=110;----**SET appropriate database compatibility level**

DECLARE #databases CURSOR FOR

SELECT NAME FROM master..sysdatabases where dbid>4

DECLARE @database_name varchar(128)

OPEN #databases

FETCH #databases INTO @database_name

WHILE @@FETCH_STATUS = 0

BEGIN

SELECT @SQL = 'ALTER DATABASE ['+ @database_name+'] SET COMPATIBILITY_LEVEL = '+CAST(@COMPATIBILITY_LEVEL as VARCHAR(10))+'';

EXEC(@SQL)

FETCH #databases INTO @database_name

END

CLOSE #databases

DEALLOCATE #databases

About The Author

Charith Silva

Charith Silva is a Microsoft certified SQL Server developer and database administrator who was graduated at Buckinghamshire New university in the UK. His career was started in 1998, primarily into Web application development, and later diversified into database development. He has got a vast experience in SQL Server database development, Database administration and Business Intelligence development. He believes that sharing the knowledge is key to the success.

Number of Posts : 87

All Posts by : Charith Silva