Change SQL Server System Stored Procedure to fix compatability level 80

sp_dbcmptlevel.sql

USE mssqlsystemresource
GO
alter procedure sys.sp_dbcmptlevel     
 @dbname sysname = NULL,     -- database name to change  
 @new_cmptlevel tinyint = NULL OUTPUT -- the new compatibility level to change to  
as  
 set nocount    on  
  
 declare @exec_stmt nvarchar(max)  
 declare @returncode int  
 declare @comptlevel float(8)  
 declare @dbid int     -- dbid of the database  
 declare @dbsid varbinary(85)  -- id of the owner of the database  
 declare @orig_cmptlevel tinyint  -- original compatibility level  
 declare @input_cmptlevel tinyint -- compatibility level passed in by user  
  ,@cmptlvl60 tinyint    -- compatibility to SQL Server Version 6.0  
  ,@cmptlvl65 tinyint    -- compatibility to SQL Server Version 6.5  
  ,@cmptlvl70 tinyint    -- compatibility to SQL Server Version 7.0  
  ,@cmptlvl80 tinyint    -- compatibility to SQL Server Version 8.0  
  ,@cmptlvl90 tinyint    -- compatibility to SQL Server Version 9.0  

--Only compatablility level 80 should be allowed to users and applications for whole instance
 select  @cmptlvl60 = 80,  
   @cmptlvl65 = 80,  
   @cmptlvl70 = 80,  
   @cmptlvl80 = 80,  
   @cmptlvl90 = 80  
  
 -- SP MUST BE CALLED AT ADHOC LEVEL --  
 if (@@nestlevel > 1)  
 begin  
  raiserror(15432,-1,-1,'sys.sp_dbcmptlevel')  
  return (1)  
 end  
  
 -- If no @dbname given, just list the valid compatibility level values.  
 if @dbname is null  
 begin  
    raiserror (15048, -1, -1, @cmptlvl60, @cmptlvl65, @cmptlvl70, @cmptlvl80, @cmptlvl90)  
    return (0)  
 end  
  
 --  Verify the database name and get info  
 select @dbid = dbid, @dbsid = sid ,@orig_cmptlevel = cmptlevel  
  from master.dbo.sysdatabases  
  where name = @dbname  
  
 --  If @dbname not found, say so and list the databases.  
 if @dbid is null  
 begin  
  raiserror(15010,-1,-1,@dbname)  
  print ' '  
  select 'Available databases:' = name  
   from master.dbo.sysdatabases  
  return (1)  
 end  
  
 -- Now save the input compatibility level and initialize the return clevel  
 -- to be the current clevel  
 select @input_cmptlevel = @new_cmptlevel  
 select @new_cmptlevel = @orig_cmptlevel  
  
 -- If no clevel was supplied, display and output current level.  
 if @input_cmptlevel is null  
 begin  
  raiserror(15054, -1, -1, @orig_cmptlevel)  
  return(0)  
 end  
  
 -- If invalid clevel given, print usage and return error code  
 -- 'usage: sp_dbcmptlevel [dbname [, compatibilitylevel]]'  
 if @input_cmptlevel not in (@cmptlvl60, @cmptlvl65, @cmptlvl70, @cmptlvl80, @cmptlvl90)  
 begin  
-- Send an email alert

DECLARE @bodyText VARCHAR(200)
SET @bodyText='User ' 
					+ CONVERT(VARCHAR,SYSTEM_USER) +' trying to change Compatibility Level of Database '
					+ CONVERT(VARCHAR,@dbname)
					+ ' at '
					+ CAST(GETDATE() AS VARCHAR(50))
EXEC msdb.dbo.sp_send_dbmail @recipients='essmess@gmail.com;', --Change Email Address Accordingly
@subject = 'Compatibility Level Change Alter',
@profile_name = 'DBTeam', --Change DB mail Profile Accordingly
@body = @bodyText,
@body_format = 'TEXT' ;


  raiserror(15416, -1, -1)  
  print ' '  
  raiserror (15048, -1, -1, @cmptlvl60, @cmptlvl65, @cmptlvl70, @cmptlvl80, @cmptlvl90)  
  return (1)  
 end  
  
 -- We should not allow the user to change the compatibility level if there exists IV or ICC  
 if @orig_cmptlevel >= @cmptlvl80 and @input_cmptlevel < @cmptlvl80  
 begin  
  -- CHECK FOR INDEXED VIEWS OR INDEXED COMPUTED-COLUMNS  
  if exists (select * from sysobjects where xtype = 'V' and id in (select id from sysindexes)) or  
   exists (select * from sysobjects o join sysindexkeys k on o.id=k.id  
    where o.xtype = 'U' and ColumnProperty(k.id, col_name(k.id, k.colid), 'IsComputed') = 1)  
  begin  
   -- Cannot set compat mode because database has a view or computed column that is indexed.  
   -- These indexes require an 8.0-compatible database.  
   raiserror(15414, -1, -1)  
   return (1)  
  end  
 end  
  
 --  Only the SA or the dbo of @dbname can execute the update part  
 --  of this procedure sys.so check.  
 if (not (is_srvrolemember('sysadmin') = 1)) and suser_sid() <> @dbsid  
  -- ALSO ALLOW db_owner ONLY IF DB REQUESTED IS CURRENT DB  
  and (@dbid <> db_id() or is_member('db_owner') <> 1)  
 begin  
  raiserror(15418,-1,-1)  
  return (1)  
 end  
  
 -- We should not allow the user to change the compatibility level for  
 -- replicated or distributed databases  
 select @comptlevel = (case @input_cmptlevel  
    when 60 then 6.0  
    when 65 then 6.5  
    when 70 then 7.0  
    when 80 then 8.0  
    when 90 then 9.0  
    end)  
  
 EXEC @returncode = master.dbo.sp_MSreplicationcompatlevel @dbname, @comptlevel  
  
 if @returncode <> 0  
 begin  
  raiserror(15306, -1, -1)  
  return (1)  
 end  
  
 -- If we're in a transaction, disallow this since it might make recovery impossible.  
 set implicit_transactions off  
 if @@trancount > 0  
 begin  
  raiserror(15002,-1,-1,'sys.sp_dbcmptlevel')  
  return (1)  
 end  
  
 -- Note: database @dbname may not exist anymore  
 -- Change compatibility level  
 -- If invoke gets error, exception will abort this proc.  
 EXEC %%DatabaseEx(Name = @dbname).SetCompatibility(Level = @input_cmptlevel)  
  
 -- Checkpoint the database that was changed.  
 select @exec_stmt = 'use ' +  quotename(@dbname, '[')   + ' checkpoint'  
 EXEC(@exec_stmt )  
  
 -- If checkpoint unsuccessful, restore the old compatibility level,  
 --  otherwise update output clevel and flush all the SPs of this database from the cache  
 if (@@error <> 0)  
 begin  
  -- If invoke gets error, exception will abort this proc.  
  EXEC %%DatabaseEx(Name = @dbname).SetCompatibility(Level = @orig_cmptlevel)  
  return (1)  
 end  
  
 dbcc flushprocindb(@dbid)  
 select @new_cmptlevel = @input_cmptlevel  
  
 return (0) -- sp_dbcmptlevel