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!