As and when new versions of SQL Server is released, Microsoft releases a list of features from the previous version which will not be supported in the future versions. This information is available on MSDN and Books Online (BOL). Here is the current list of features that will be deprecated in the next version of SQL Server.
Suppose I have a lot of instances running SQL Server 2008 and those databases cater to hundreds of applications. Now that I came know that several features will be deprecated in the next version, I am curious to know if those applications are using any of those features. I just want to be sure that none of the applications break when I upgrade to the next release of SQL Server. With quite a few features listed in the document, it is humanly impossible to search the application/stored procedure source code to verify if any of them are using the deprecated features.
SQL Server 2008 makes your job easy. You can get the list of deprecated features in SQL Server 2008 with a simple query.
SELECT * FROM sys.dm_os_performance_counters WHERE OBJECT_NAME like '%deprecated%' order by cntr_value desc
The output lists 228 deprecated features in SQL Server 2008.
In this output the value of cntrl_value indicates the number of times the deprecated feature has been used since the last restart of the SQL Server service. In this case DATABASEPROPERTY is the one which has been used the maximum number of times (this instance has a database which is used for a legacy monitoring system).
Now that we know that there is a DMV which helps us to figure out the usage of deprecated features, it is upto to your imagination to figure out which stored procedure or application is making use of those. The following query will search all the stored procedures in a database for the instance_name values reported in the sys.dm_os_performance_counters DMV.
SELECT DISTINCT name FROM sys.all_objects INNER Join sys.dm_os_performance_counters opc ON Object_Definition (object_id) Like '%' + opc.instance_name + '%' WHERE Type = 'P' And is_ms_shipped = 0 ORDER BY Name