Find UnUsed Stored Procedures in Sql 2008

Question

Hello, How I can Find the right way and more reliable to find the UnUsed Stored Procedure in Sql server 2008? Thank You for your help!

Rajanikant Hawaldar · Answer

SELECT p.name AS 'SP Name' -- Get list of all SPs in the current databaseFROM sys.procedures AS pWHERE p.is_ms_shipped = 0EXCEPTSELECT p.name AS 'SP Name' -- Get list of all SPs from the current database FROM sys.procedures AS p -- that are in the procedure cacheINNER JOIN sys.dm_exec_procedure_stats AS qsON p.object_id = qs.object_idWHERE p.is_ms_shipped = 0; https://www.sqlservercentral.com/articles/how-to-find-unused-stored-procedures-in-sql-server-20052008

Vishvjit Shinde · Answer

This will work : select sp.name as 'Name' from sys.procedures as sp where sp.is_ms_shipped =0 Except select sp.name as 'Name' from sys.procedures as sp JOIN sys.dm_exec_procedure_stats as qs on sp.object_id = qs.object_id where sp.is_ms_shipped =0;

Vishvjit Shinde · Answer

Hi, All Database with Procedure Name Try Like this: DECLARE @ name sysname; DECLARE @sql nvarchar( max ) = ' SELECT DB_NAME() AS [database_name], OBJECT_SCHEMA_NAME(object_id) AS [schema_name], name AS [procedure_name] FROM sys.procedures '; DECLARE @theSQL nvarchar( max ); DECLARE @results TABLE ( [database_name] sysname, [schema_name] sysname, [procedure_name] sysname ); DECLARE dbs CURSOR STATIC LOCAL FORWARD_ONLY READ_ONLY FOR SELECT name FROM sys.databases; -- you may want to exclude system databases here -- WHERE name NOT IN ('master', 'model', 'msdb', 'tempdb', 'distribution') OPEN dbs; FETCH NEXT FROM dbs INTO @ name ; WHILE @@FETCH_STATUS = 0 BEGIN SET @theSQL = 'EXEC ' + QUOTENAME(@ name ) + '.sys.sp_executesql @sql' ; INSERT @results EXEC sys.sp_executesql @theSQL, N '@sql nvarchar(max)' , @sql FETCH NEXT FROM dbs INTO @ name ; END CLOSE dbs; DEALLOCATE dbs; SELECT * FROM @results; If you want all procedures from same Database: select schema_name(obj.schema_id) as schema_name, obj. name as procedure_name, case type when 'P' then 'SQL Stored Procedure' when 'X' then 'Extended stored procedure' end as type, substring (par.parameters, 0, len(par.parameters)) as parameters, mod.definition from sys.objects obj join sys.sql_modules mod on mod.object_id = obj.object_id cross apply ( select p. name + ' ' + TYPE_NAME(p.user_type_id) + ', ' from sys.parameters p where p.object_id = obj.object_id and p.parameter_id != 0 for xml path ( '' ) ) par (parameters) where obj.type in ( 'P' , 'X' ) order by schema_name, procedure_name;

p s · Answer

Thank you!

p s · Answer

Thanks Rajnikant for your sql query. I am also running similar type of query but looking to include all the database and Procedure in one query.

p s · Answer

Thanks Vishvjit. I am running similar query but i am looking more in details and i am looking all the database listing procedure together with like Database NAme Procedure and if posible need to include why this procedure is candidate as UnUsed Stored Procedure. Appreciate your help!

Find UnUsed Stored Procedures in Sql 2008

Answers (6)