Find UnUsed Stored Procedures in Sql 2008

p s

6y1.3k

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!

6 Replies

Know the answer? Post it — somebody with the same question will find it here.

Rajanikant Hawaldar6y ago
Accepted 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
0
Vishvjit Shinde6y ago
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;
0
p s6y ago
Thank you!
0
p s6y ago
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.
0
p s6y ago
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!
0
Vishvjit Shinde6y ago
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;
+3