You can easily determine whether existing stored procedures, UDFs and views can work in a higher (or lower, if you wish) compatibility level. The only tool that you need for this is SSMS and a copy of your production database on a development server. Here are the steps:

1. In SQL Server Management Studio, click Stored Procedures and open the Object Explorer Details window (F7)
2. Select all SPs, right-click and select “CREATE TO” then “New Query Editor Window”. This will produce a single script to re-create all of your existing stored procedures. Save that script before you proceed to the next step.
3. Now go back to the Object Explorer Details window, select all SPs again and then Delete them all. (Feels good, huh? )
4. Next, go and set the database compatibility level to the desired value: open the database properties, go to Options, set the compatibility level to another value, click OK
5. Now open the script you saved in the step 1 and execute it. If it finishes without any errors then your code doesn’t require any changes. Otherwise it will show errors indicating which parts are not supported, for example:

USE [AdventureWorks2012]
GO
SELECT TOP 10 * FROM [Production].[BillOfMaterials]
WITH myCTE AS (
        SELECT TOP 10 * FROM [Production].[Product]
        )
SELECT * FROM myCTE

Will throw the following error:

Msg 336, Level 15, State 1, Line 4
Incorrect syntax near ‘myCTE’. If this is intended to be a common table expression, you need to explicitly terminate the previous statement with a semi-colon.

The error means that in this compatibility level (90 and up) the statement before the CTE must end with a semicolon. In compatibility level 80 that was not mandatory.

The same procedure can be used for UDFs and views, too. The method will show you exactly what you need to change in your code and where. This is especially helpful when your database has a large number of database objects.