Learn more about SQL Server tools


Latest from MSSQLTips

Identifying Deprecated SQL Server Code with Profiler

MSSQLTips author Jeremy Kadlec By:   |   Read Comments (9)   |   Related Tips: More > Deprecated Features

In your recent tip (WARNING: SQL Server Deprecated Features) you have outlined a number of deprecated features.  The listing with the mapping to the new code set is beneficial.  Unfortunately I have an enterprise SQL Server environment and I need to be able to identify the deprecated features in more of an automated manner.  We do not have the time to check all of our code (stored procedures, functions, etc.) and I know we have T-SQL embedded in middle tier and front end applications.  How can I analyze this code in an efficient manner?

In any sized environment, identifying the deprecated SQL Server features could be a long and time consuming process.  Luckily, Microsoft was thinking ahead and has events in Profiler that can be captured to identify the code with deprecated features.  Let's take a look at how to capture the T-SQL code:

Launch SQL Server 2005 Profiler

Navigate to Start | All Programs | Microsoft SQL Server 2005 | Performance Tools | SQL Server Profiler.


Start a New Trace

Navigate to File | New Trace... | Authenticate to the SQL Server you want to monitor


General Tab - Configurations

Specify the name and configure the results to be saved to a table.


Events Selection Tab - Configurations

In order to review all events, enable the check box for 'Show all events' and 'Show all columns' on the bottom right of the screen.

Then navigate to the 'Deprecation' heading and enable the 'Deprecation Announcement' and 'Deprecation Final Support' events.

Once you have made these configurations or others as desired, then press the 'Run' button to begin the Profiler session.


Profiler Results - Deprecated Features

As an example, if you issue this command:


The results in Profiler will be:


Additional Analysis - Query the Results

FROM <TableName>
WHERE EventClass IN (125, 126);

Next Steps


Last Update: 11/9/2007

About the author
MSSQLTips author Jeremy Kadlec
Since 2002, Jeremy Kadlec has delivered value to the global SQL Server community as an Edgewood Solutions SQL Server Consultant, MSSQLTips.com co-founder and Baltimore SSUG co-leader.

View all my tips
Related Resources

print tip Print  
Become a paid author

Learn more about SQL Server tools

Post a comment or let the author know this tip helped you.

       All comments are reviewed, so stay on subject or we may delete your comment.

*Name   *Email Notify for updates

       Note: your email address is not published. Required fields are marked with an asterisk (*)

Get free SQL tips:

*Enter Code refresh code     

Tuesday, November 04, 2014 - 9:32:07 AM - Eric S Read The Tip

I've run into the same issue as Randy M. It looks like the deprecation feature is not available for 2000 databases as when I target a 2008 R2 database, the option does appear using the same sql profiler version. Has any one found a way to look for deprecated SQL in a 2000 database?

Friday, November 08, 2013 - 11:54:05 AM - Jeremy Kadlec Read The Tip


Great question.  First I would run Profiler for a representative amount of time to capture all of your critical processes to verify all you do not have any deprecated code.  I would also change the database compatibility mode in your Dev and Test environments well in advance of production.  Then test your application in your Dev and Test environments to ensure there are no issues.


Thank you,
Jeremy Kadlec
Community Co-Leader


Thursday, November 07, 2013 - 6:34:47 AM - Alessio Read The Tip

Hi Jeremy

I would like to ask you a suggestion.

I've a (large) db on SQLServer 2008 R2, in compatibility level 80 and i would like set it to compatibility level 100

Do you think that are sufficient run profiler to identify deprecated features before change the compatibility level?

Do you have other suggestions ?

Thank you in advance.

Saturday, October 12, 2013 - 8:37:58 PM - Jeremy Kadlec Read The Tip


Let me see if I can find a good test environment to test out that scenario.

Thank you,
Jeremy Kadlec
Community Co-Leader

Monday, September 23, 2013 - 4:37:38 PM - Randy M Read The Tip

The server I'm trying to get the Deprecated Events from is a SQL 2000 box. I'm trying to run Profiler from a SQL 2005 server against the 2000 - build 2187 box. Unfortunately, the Deprecation Events don't appear as a choice whether you check "show all Events" or not. I was wondering if you had any idea's on how you would proceed. 

Sunday, September 22, 2013 - 3:49:23 PM - Jeremy Kadlec Read The Tip


I am not sure I understand your question 100%, but I am wondering if you did not select the check box for "Show all Events" as shown in the 4th image above.  See if that helps.

Thank you,
Jeremy Kadlec
Community Co-Leader

Thursday, September 19, 2013 - 12:45:24 PM - Randy M Read The Tip

Hay Jeremy, I realize this is an older article but, I'm trying this out on a SQL 2005 Std instance with Service Pack 4 and it doesn't have the Deprecation Events that you show in the picture. The Events aren't in this machines profiler. Any idea's why?

Thursday, September 16, 2010 - 11:50:52 AM - Admin Read The Tip

Good question.  In this circumstance, you want to run the code on the SQL Server 2008 instance.

Thank you,
The MSSQLTips Team

Thursday, September 16, 2010 - 9:54:26 AM - Tony Henley Read The Tip
To be proactive, do you need to run profiler on the SQL Server version, to which you are migrating? (i.e you are on SQL 2008 and want to migrate to SQL 2008 R2, so you should run profiler on SQL Server 2008 R2?)

More SQL Server Solutions



Get Free SQL Tips










BI Professionals


Q and A

Today's Tip







Tip Categories

Search By TipID



First Timer?


Free T-shirt



User Groups

Author of the Year

More Info








Copyright (c) 2006-2015 Edgewood Solutions, LLC All rights reserved
Some names and products listed are the registered trademarks of their respective owners.