Link to home
Start Free TrialLog in
Avatar of bibi92
bibi92Flag for France

asked on

script to change compatibility level on all databases if level equal 90

Hello,

I search a script to put all databases SINGLE_USER, change the compatibility level to 100 if it is equal to 90 and put all databases MULTI_USER

Thanks

Regards
Avatar of Zberteoc
Zberteoc
Flag of Canada image
This script will generate the commands to do that. Run it in Management Studio in a Query Window and then in the result panel select the result and copy it with no headers by right clicking on it and choose the "Copy" option or just hit Ctrl+C on your keyboard after you selected it. Open a new Query Window, paste the result in it and then execute the entire script.

USE [master]
GO

declare
	@sql varchar(8000)=''
select 
	@sql=@sql+'-- ### changing ['+[name]+'] database 	
ALTER DATABASE ['+[name]+'] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
ALTER DATABASE ['+[name]+'] SET compatibility_level = 100;
GO
ALTER DATABASE ['+[name]+'] SET MULTI_USER WITH NO_WAIT
GO

'
from 
	sys.databases 
where
	[compatibility_level]=90

select @sql='USE [master]
GO

'+@sql

select @sql

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of DcpKing
DcpKing
Flag of United States of America image
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of bibi92
bibi92
Flag of France image

ASKER

Thanks
Avatar of Zberteoc
Zberteoc
Flag of Canada image
No offence, but what was wrong with my script? I posted it before
DcpKing. Have you even tried it?