Find Unused Objects in your SQL Server Database

Here’s a disclaimer - There is no easy way of doing this!

Here are a couple of options that you can use to find unused objects in your database:

1. One option is to run the profiler, capture the results and analyze if any objects are used at all. This should be done for a certain period of time. If any application is connected to the database, run each and every functionality available in front end and, capture and analyze the profiler result.

2. Another method is to rename certain objects that you think are not being used and observe the logs over a period of time and check for any code breaks. This is based on a trial-and-error method.

3. Another alternative is via a query

SELECT source_code,last_execution_time
FROM sys.dm_exec_query_stats as stats
CROSS APPLY (
SELECT text as source_code
FROM sys.dm_exec_sql_text(sql_handle))
AS query_text
ORDER BY last_execution_time desc

This query wont give you a list of unused objects. It gives you a list of used objects which you need to keep track for some time and see if any of the objects are not used. It is like monitoring trace result for sometime to determine if any objects are not used.