Change compatibility level of all databases after an upgrade

You make an upgrade to SQL 2016 from SQL 2014, one thing you have to watch out for is the compatibility levels of the upgraded databases. By default, all the databases will be at the compatibility level of the older version. They need to be changed manually to make use of all the new features of the upgraded version. Below is a script that can take care of that part for you!


set quoted_identifier off
declare @procname varchar(100)
declare @string varchar(1000)
declare cursor1 cursor for
select name from sys.databases where compatibility_level=120
open cursor1
fetch next from cursor1 into @procname
while (@@fetch_status = 0)
begin
select @string="alter database "+@procname +" set compatibility_level = 130"
print @string
fetch next from cursor1 into @procname
end
close cursor1

deallocate cursor1