How to change the compatibility level to 9.0

I have a SQL 2005 server that has more than 90 databases, and I need to change the compatibality level to 9.0. Is there an easy way to do this in SQL? any help is greatly appreciated.

Emad

I don't know if it can be called easy, but the following script should do the work, by using the stored procedure sp_dbcmptlevel , to update all databases whose compatibility_level is not equal to 90.

Hope this Helps!!!

Roberto Hernandez-Pou
http://community.rhpconsulting.net

DECLARE DATABASES_80 CURSOR
READ_ONLY
FOR SELECT NAME FROM SYS.DATABASES WHERE COMPATIBILITY_LEVEL != '90'

DECLARE @.DATABASENAME NVARCHAR(255)
DECLARE @.COUNTER INT

-- INITIALIZE VARIABLES
SET @.COUNTER = 1

-- OPEN CURSOR
OPEN DATABASES_80

FETCH NEXT FROM DATABASES_80 INTO @.DATABASENAME
WHILE (@.@.fetch_status <> -1)
BEGIN
IF (@.@.fetch_status <> -2)
BEGIN

-- CHANGE DATABASE COMPATIBILITY
EXECUTE sp_dbcmptlevel @.DATABASENAME , '90'

-- PRINT
PRINT '(' + RIGHT(('000' + CAST(@.COUNTER AS NVARCHAR(255))),3) + ') - CHANGED DATABASE COMPATIBILITY LEVEL FOR: ' + @.DATABASENAME

-- INCREASE COUNTER
SET @.COUNTER = @.COUNTER + 1

END

FETCH NEXT FROM DATABASES_80 INTO @.DATABASENAME
END

CLOSE DATABASES_80
DEALLOCATE DATABASES_80

GO

sql