Roilis Roilis - 1 year ago
404 0

This script can be used to change the compatibility level of a SQL Server database.
Use this to upgrade older databases (SQL Server 2005/2008) to newer versions (2012/2014).

SQL

Change database compatibility level (2012)

ALTER DATABASE MyDatabase SET COMPATIBILITY_LEVEL = 110;

DECLARE @sql NVARCHAR(MAX) = N'';

SELECT @sql += CHAR(13) + CHAR(10) + 'UPDATE STATISTICS '
  + QUOTENAME(SCHEMA_NAME(schema_id)) 
  + '.' + QUOTENAME(name) + ' WITH FULLSCAN;'
  FROM sys.tables;

PRINT @sql;
SQL

Change database compatibility level (2014)

ALTER DATABASE MyDatabase SET COMPATIBILITY_LEVEL = 120;

DECLARE @sql NVARCHAR(MAX) = N'';

SELECT @sql += CHAR(13) + CHAR(10) + 'UPDATE STATISTICS '
  + QUOTENAME(SCHEMA_NAME(schema_id)) 
  + '.' + QUOTENAME(name) + ' WITH FULLSCAN;'
  FROM sys.tables;

PRINT @sql;