locked
Find UnUsed Stored Procedure RRS feed

  • Question

  • I am trying to use the script to get unused Stored Procedure reports but having confusion as which one is the best approach as to look into cache or recompilation.
    I don't want to run the script in each database and do the analysis.
    Is it any way i can include in a one script and it's also gives me Database name, last access, scan or look up or not in cache to make easy for analysis.
    Any Idea?

    I tried this one but i have to run for each database:

    WITH UnUsed (id)
    AS
    (
    SELECT s.object_id
    FROM sys.procedures AS s
    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

    I also tried this one with the counts but not sure to make decision:

    SELECT
    DatabaseName = DB_NAME(st.dbid)
    ,SchemaName = OBJECT_SCHEMA_NAME(st.objectid,dbid)
    ,StoredProcedure = OBJECT_NAME(st.objectid,dbid)
    ,ExecutionCount = MAX(cp.usecounts)
    FROM sys.dm_exec_cached_plans cp
    CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) st
    WHERE DB_NAME(st.dbid) IS NOT NULL
    AND cp.objtype = 'proc'
    AND DB_NAME(st.dbid) NOT IN ('tempdb', 'msdb', 'master', 'model')
    GROUP BY
    cp.plan_handle
    ,DB_NAME(st.dbid)
    ,OBJECT_SCHEMA_NAME(objectid,st.dbid)
    ,OBJECT_NAME(objectid,st.dbid)

    Anyone has better solution really appreciate it?

    Thanks for help!

    Thursday, March 5, 2020 3:43 PM

All replies

  • Hi pdsqsql,

    Try code as:

    SELECT DB_NAME(ius.[database_id]) AS [Database],
    OBJECT_NAME(ius.[object_id]) AS [TableName],
    MAX(ius.[last_user_lookup]) AS [last_user_lookup],
    MAX(ius.[last_user_scan]) AS [last_user_scan],
    MAX(ius.[last_user_seek]) AS [last_user_seek] FROM sys.dm_db_index_usage_stats AS ius
    WHERE ius.[database_id] = DB_ID()
    AND ius.[object_id] = OBJECT_ID('YourTableName')
    GROUP BY ius.[database_id], ius.[object_id];

    Best Regards.

    yuxi


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to  MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Monday, March 9, 2020 7:55 AM
  • Thanks Yuxi.

    This is for UnUsed tables and i am already running but i am looking for Unused Stored Procedure.

    If you have better solution, please let me know.

    Monday, March 9, 2020 2:48 PM
  • Hi pdsqsql,

    This may be helpful:

    identifying-unused-stored-procedureshow-to-find-the-list-of-unused-stored-procedures-in-sql-server-2005

    Best Regrds.

    yuxi


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to  MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Thursday, March 19, 2020 5:22 AM