I was given the task of finding those unused procs/functions in our SQL 2012 database.
Initially my plan was to use the table sys.dm_exec_procedure_stats. I could set up a job to check the table once/day and then summarize over the course of a month or a quarter.
Two problems with this: it wouldn't catch the functions and wouldn't catch stored procs with NOCOMPILE. So I decided to use extended events instead. I didn't find the code to accomplish this exactly, so I'm adding it here in case it could save someone else some time.
CREATE EVENT SESSION [CaptureProcNames] ON SERVER ADD EVENT sqlserver.sp_statement_starting ( SET collect_object_name=(1), collect_statement=(0) WHERE ( ([object_type]=(8272) OR [object_type]=(20038)) AND [sqlserver].[database_name]=N'MyDatabase' AND [sqlserver].[is_system]=(0)) ) ADD TARGET package0.event_file (SET filename=N'C:\Program Files\Microsoft SQL Server\MSSQL11.KAIGSTAGING\MSSQL\Log\CaptureProcNames.xel', max_file_size=(250), max_rollover_files=(3)) WITH (MAX_MEMORY=4096 KB, EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS, MAX_DISPATCH_LATENCY=30 SECONDS, MAX_EVENT_SIZE=0 KB, MEMORY_PARTITION_MODE=NONE, TRACK_CAUSALITY=OFF, STARTUP_STATE=OFF)