Identifying Deprecated SQL Server Code with Profiler

By:   |   Updated: 2007-11-09   |   Comments (9)   |   Related: 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


get scripts

next tip button

About the author
MSSQLTips author Jeremy Kadlec Jeremy Kadlec is the Co-Founder, Editor and Author at, CTO @ Edgewood Solutions and a six time SQL Server MVP.

View all my tips

Article Last Updated: 2007-11-09

Comments For This Article

Tuesday, November 4, 2014 - 9:32:07 AM - Eric S Back To Top (35179)

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 8, 2013 - 11:54:05 AM - Jeremy Kadlec Back To Top (27450)


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 7, 2013 - 6:34:47 AM - Alessio Back To Top (27436)

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 Back To Top (27135)


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 Back To Top (26901)

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 Back To Top (26891)


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 Back To Top (26866)

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 Back To Top (10164)

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 Back To Top (10163)
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?)


get free sql tips
agree to terms