Identify Deprecated SQL Server Code with Extended Events

--Find the additional columns that can be tracked
SELECT *
FROM sys.dm_xe_objects xo INNER JOIN sys.dm_xe_packages xp
ON xp.[guid] = xo.[package_guid]
WHERE xo.[object_type] = 'action'
ORDER BY xp.[name];
GO

--Find the columns that are  available to track for the
--deprecation_announcement event
SELECT * FROM sys.dm_xe_object_columns
WHERE [object_name] = 'deprecation_announcement';
GO

--Find the columns that are  available to track for the
--deprecation_final_support event
SELECT * FROM sys.dm_xe_object_columns
WHERE [object_name] = 'deprecation_final_support';
GO

--We are looking at deprecated features
ADD EVENT sqlserver.deprecation_announcement

(
--Add additional columns to track
ACTION (sqlserver.database_id, sqlserver.sql_text, sqlserver.session_id,
sqlserver.tsql_stack, sqlserver.username, sqlserver.client_hostname, sqlserver.database_context)
),

--We are looking at discontinued features
ADD EVENT sqlserver.deprecation_final_support
(
--Add additional columns to track
ACTION (sqlserver.database_id, sqlserver.sql_text, sqlserver.session_id, sqlserver.tsql_stack))

--As a best practice use asynchronous file target, reduces overhead.
ADD TARGET package0.asynchronous_file_target(
SET filename='c:\Monitor_Deprecated_Discontinued_features.etl', metadatafile='c:\Monitor_Deprecated_Discontinued_features.mta')
GO

--Now start the session
ALTER EVENT SESSION Monitor_Deprecated_Discontinued_features ON SERVER
STATE = START;
GO

--String alias
SELECT 'FullName' = FirstName + ' ' + MiddleName + '. ' + LastName
FROM Person.Person
GO

--# table name
CREATE TABLE #(c1 INT)
GO

--3 part column name
SELECT TOP 1 AdventureWorks2008.Person.Person.LastName, *
FROM Person.Person
SELECT TOP 1 Person.Person.LastName, *
FROM Person.Person
GO

--use :: for function calls
SELECT * FROM ::fn_virtualfilestats(2,1)
GO

--using hints without WITH keyword
SELECT TOP 1 Person.LastName, * FROM Person.Person (NOLOCK)
GO

--use fn_get_sql
DECLARE @Handle VARBINARY(64);
SELECT @Handle = sql_handle
FROM sys.dm_exec_requests
WHERE session_id = @@SPID AND request_id = 0;
SELECT * FROM sys.fn_get_sql(@Handle);
GO

--Drop the event if it already exists
DROP EVENT SESSION Monitor_Deprecated_Discontinued_features ON SERVER; 
GO