Looking For Unused Stored Procedures In SQL Server 2008 Posted on July 8, 2009 One thing you should periodically do as a “good” DBA, is to look for unused objects in your user databases, to make the database easier to maintain and understand. If you use T-SQL stored procedures, (which I highly recommend), you can use the queries below to come up with a list of stored procedures that are not in your procedure cache. Depending on how long since your procedure cache has been flushed, and your workload, this can give you a pretty good list to start with. You need to remember that if a SP has WITH RECOMPILE or OPTION(RECOMPILE), it won’t be in the cache. Also, just because a stored procedure shows up on this list does not mean that you should just blindly delete it. I would search your source code (if possible), and talk to your developers. Then, I would rename the SP (rather than deleting it) in a test environment, and regression test. I like to use a prefix of zzz for the renamed stored procedures, so that they all sort together in the SSMS Object Explorer Window. -- Get list of all SPs in the current database (SQL 2005 and 2008) SELECT p.name AS 'SP Name', p.create_date, p.modify_date FROM sys.procedures AS p WHERE p.is_ms_shipped = 0 ORDER BY p.name; -- Get list of possibly unused SPs (SQL 2008 only) SELECT p.name AS 'SP Name' -- Get list of all SPs in the current database FROM sys.procedures AS p WHERE p.is_ms_shipped = 0 EXCEPT SELECT p.name AS 'SP Name' -- Get list of all SPs from the current database FROM sys.procedures AS p -- that are in the procedure cache INNER JOIN sys.dm_exec_procedure_stats AS qs ON p.object_id = qs.object_id WHERE p.is_ms_shipped = 0; You can also use this query to check dependencies (in SQL 2008) on an object: SELECT referencing_schema_name, referencing_entity_name FROM sys.dm_sql_referencing_entities (‘schemaname.objectname’, 'OBJECT');
