Hi I am trying to do a little cleanup by determining if there are any SQL Stored Procedures that are not being used anymore ! If I run the following code on my local instance of SQL server I see the results I am looking for........a list of SP that have not been used since the last time SQL server has been started. !!!!!!!! WITH UnUsed (id) AS ( SELECT s.object_id FROM sys.procedures AS s WHERE name NOT LIKE 'dt_%' Except SELECT dm.object_id FROM sys.dm_exec_procedure_stats AS dm ) SELECT s.name, s.type_desc FROM UnUsed JOIN sys.procedures s ON UnUsed.id = s.object_id !!!!!!!! BUT When I run it at DASP SQL Server I get the following message Msg 297, Level 16, State 1, Line 1 The user does not have permission to perform this action. It is failing for the: SELECT dm.object_id FROM sys.dm_exec_procedure_stats Question: - Is there another way I can get the results I want ? Thanks for your help in advance Frank
Executing sys.dm_exec_procedure_stats function requires at least VIEW SERVER STATE permission on server. This is not allowed on our shared production sql servers for the security reason. I am afraid it is not possible for a database owners on our shared servers to acquire this statistics.
Dmitri Thanks for your response. OK I can accept that. Is there any other ".sys" table OR a different way I can determine if there are any unused SQL SP, I am not using anymore? Thanks albanello
There is no such as statistics on the server that you will be able to query. You can modify your stored procedures inserting a t-sql code that will increment a number it was called and log it to table. You will be able then to analyze this table and see which stored procedures were or were not called and how many time.
Dmitri Thanks, that looks like one solution, that would work. It requires a lot of work on my part ie I have to add the T-SQL code to EVERY Stored Procedure that is returned by the "sys.procedures " table. I was hoping for something a little less labor intensive, such as my first post. But thanks for the idea. Is there anybody else out there that has dealt with this problem? (Cleaning out old Stored Procedures that are not used anymore) albanello
