Change compatibility level to SQL 2012 or 2014 in a Blackbaud CRM database

bbcrm_compatibility110-120.sql

/* *******************************************************************
 * Execute this script against the CRM, OLTP database to be upgraded 
 * ******************************************************************* */


/* Determine your SQL Server version and change compatibility level highest available */
declare @version nvarchar(8), @sql nvarchar(max)
select @version =
	case
		when @@VERSION like '%SQL Server 2014%' then '110' -- BB and MS are currently reviewing 120 support
		when @@VERSION like '%SQL Server 2012%' then '110'
	end
set @sql = 'ALTER DATABASE ' + QUOTENAME(db_name()) + ' SET COMPATIBILITY_LEVEL = ' + @version
exec sp_executesql @sql;
GO


/* Runs UPDATE STATISTICS against all user-defined and internal tables in the current database. */
EXEC sp_updatestats;
GO


/* Rebuild (re-enable) all CONSTITUENT and SEARCHCONSTITUENT indexes */
ALTER INDEX ALL ON dbo.[CONSTITUENT] REBUILD;
IF object_id('dbo.SEARCHCONSTITUENT') is not null
	ALTER INDEX ALL ON dbo.[SEARCHCONSTITUENT] REBUILD;
GO


/* Find disabled CONSTITUENT foreign keys and generate script to enable them */
declare @tableName nvarchar(181), @fkName nvarchar(181), @cmd nvarchar(max)
declare alterTableCursor cursor for
select distinct o.name as tableName, f.name as fkName
from sys.foreign_keys f
	inner join sys.objects o on f.parent_object_id = o.object_id
	inner join sys.schemas s on o.schema_id = s.schema_id
where (f.object_id in
		(	select fk.constraint_object_id from sys.foreign_key_columns as fk
			where fk.referenced_object_id =
				(select object_id from sys.tables INNER JOIN sys.schemas ON sys.tables.schema_id = sys.schemas.schema_id
				where sys.tables.name = 'CONSTITUENT' and sys.schemas.name = 'dbo')
		) 
	or o.name = 'CONSTITUENT')
	and f.is_disabled = 1 
	and s.name = 'dbo'
order by o.name, f.name

open alterTableCursor
fetch next from alterTableCursor into @tableName, @fkName
while @@FETCH_STATUS = 0
	begin
		begin try
			set @cmd = 'ALTER TABLE dbo.' + quotename(@tableName) + ' WITH CHECK CHECK CONSTRAINT ' + quotename(@fkName) + ';'
			exec (@cmd);
			print 'Foreign Key ' + @fkName + ' on table ' + @tableName + ' is now enabled and trusted.'
		end try
		begin catch
			DECLARE @ErrorMessage NVARCHAR(4000), @ErrorSeverity INT, @ErrorState INT
			SELECT
				@ErrorMessage = ERROR_MESSAGE(),
				@ErrorSeverity = ERROR_SEVERITY(),
				@ErrorState = ERROR_STATE();
			RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState);
			set @cmd = 'ALTER TABLE dbo.' + quotename(@tableName) + ' CHECK CONSTRAINT ' + quotename(@fkName) + ';'
			exec (@cmd);
			print 'Foreign Key ' + @fkName + ' on table ' + @tableName + ' is now enabled but untrusted because existing data violates the foreign key relationship.'
		end catch
		fetch next from alterTableCursor into @tableName, @fkName
	end
close alterTableCursor
deallocate alterTableCursor
GO


-- Find disabled check constraints and generate script to enable them
declare @tableName nvarchar(181), @ccName nvarchar(181), @cmd nvarchar(max)
declare alterTableCursor cursor for
select distinct o.name as tableName, c.name as ccName
from sys.check_constraints c
	inner join sys.objects o ON c.parent_object_id = o.object_id
	inner join sys.schemas s on o.schema_id = s.schema_id
where c.is_disabled = 1
	and s.name = 'dbo'
	and
		(c.definition like '%UFN_VALIDWEBADDRESS%'
		or c.definition like '%UFN_VALIDEMAILADDRESS%')
order by o.name, c.name
 
open alterTableCursor
fetch next from alterTableCursor into @tableName, @ccName
while @@FETCH_STATUS = 0
	begin
		begin try
			set @cmd = 'ALTER TABLE dbo.' + quotename(@tableName) + ' WITH CHECK CHECK CONSTRAINT ' + quotename(@ccName) + ';'
			exec (@cmd);
			print 'Check Constraint ' + @ccName + ' on table ' + @tableName + ' is now enabled and trusted.'
		end try
		begin catch
			DECLARE @ErrorMessage NVARCHAR(4000), @ErrorSeverity INT, @ErrorState INT
			SELECT
				@ErrorMessage = ERROR_MESSAGE(),
				@ErrorSeverity = ERROR_SEVERITY(),
				@ErrorState = ERROR_STATE();
			RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState);
			set @cmd = 'ALTER TABLE dbo.' + quotename(@tableName) + ' CHECK CONSTRAINT ' + quotename(@ccName) + ';'
			exec (@cmd);
			print 'Check Constraint ' + @ccName + ' on table ' + @tableName + ' is now enabled but untrusted because existing data violates the check constraint.'
		end catch
		fetch next from alterTableCursor into @tableName, @ccName
	end
close alterTableCursor
deallocate alterTableCursor
GO


/* Reports and corrects pages and row count inaccuracies in the catalog views. */
declare @db nvarchar(181) = db_name()
DBCC UPDATEUSAGE(@db) WITH NO_INFOMSGS, COUNT_ROWS;
GO


/* Checks the logical and physical integrity of all the objects in the specified database
 * Fixes sys.tables with has_unchecked_assembly_data = 1  */
declare @db nvarchar(181) = db_name()
DBCC CHECKDB(@db) WITH ALL_ERRORMSGS, NO_INFOMSGS, DATA_PURITY;
GO