How to check and change Compatibility Level in SQL Server?

How to check and change Compatibility Level in SQL Server:


Script to check Compatibility level of db:

Use master

SELECT @@servername as ServerName,NAME,COMPATIBILITY_LEVEL,version_name =
CASE compatibility_level
    WHEN 65  THEN 'SQL Server 6.5'
    WHEN 70  THEN 'SQL Server 7.0'
    WHEN 80  THEN 'SQL Server 2000'
    WHEN 90  THEN 'SQL Server 2005'
    WHEN 100 THEN 'SQL Server 2008/R2'
    WHEN 110 THEN 'SQL Server 2012'
    WHEN 120 THEN 'SQL Server 2014'
END,LOG_REUSE_WAIT_DESC,recovery_model_desc,state_desc,* from sys.databases
where Name in ('DBName')

Normally Less Traffic DB you can issue following Statement to change compatibility level without any issues

Script:

ALTER DATABASE DBNAME
SET COMPATIBILITY_LEVEL = 100---------for SQL 2008 R2

High Traffice DB you can issue following statement for security and safety

ALTER DATABASE  DBNAME SET SINGLE_USER

ALTER DATABASE DBNAME
SET COMPATIBILITY_LEVEL = 110--------for SQL 2012

ALTER DATABASE DBNAME SET MULTI_USER


After/Before Change Compatbility level you can check one objects(SPs,Views,Triggers,UDF's)
by executing one by one.

To know about objects see below

Triggers:
select * from sys.triggers--triggers

Procedures:
select * from sys.procedures

Views:
select * from sys.views--views
where name NOT LIKE '%syncobj%'

UDF's:
---function--
SELECT DISTINCT o.name AS ObjectName,
CASE o.xtype
WHEN 'C' THEN 'CHECK constraint'
WHEN 'D' THEN 'Default or DEFAULT constraint'
WHEN 'F' THEN 'FOREIGN KEY constraint'
WHEN 'FN' THEN 'Scalar function'
WHEN 'IF' THEN 'In-lined table-function'
WHEN 'K' THEN 'PRIMARY KEY or UNIQUE constraint'
WHEN 'L' THEN 'Log'
WHEN 'P' THEN 'Stored procedure'
WHEN 'R' THEN 'Rule'
WHEN 'RF' THEN 'Replication filter stored procedure'
WHEN 'S' THEN 'System table'
WHEN 'TF' THEN 'Table function'
WHEN 'TR' THEN 'Trigger'
WHEN 'U' THEN 'User table'
WHEN 'V' THEN 'View'
WHEN 'X' THEN 'Extended stored procedure'
ELSE o.xtype
END AS ObjectType,
ISNULL( p.Name, 'Location') AS Location
FROM syscomments c
INNER JOIN sysobjects o ON c.id=o.id
LEFT JOIN sysobjects p ON o.Parent_obj=p.id
where o.xtype in ('FN','IF','TF')