如何查找过去3个月内未使用的存储过程 [英] How to find which stored procedure not used in last 3 months
问题描述
如何识别过去3个月内未使用的存储过程。
How do I identify which stored procedures are not using from last 3 months.
我使用以下查询查找但未获得所需结果
I use the below query to find it but not get desired result
- 获取可能未使用的SP列表（仅限SQL 2008）
  SELECT p.name AS'SP Name'，s.name       - 获取当前数据库中所有SP的列表
  FROM sys.procedures AS p
内部联接sys.schemas AS s on p.schema_id = s.schema_id
 在哪里p.is_ms_shipped = 0和s.schema_id = 9
 除
  SELECT p.name AS'SP Name'，s.name       - 获取当前数据库中所有SP的列表
  FROM sys.procedures AS p         - 在程序缓存中的数字
  INNER JOIN sys.dm_exec_procedure_stats AS qs ON p.object_id = qs.object_id
内部联接sys.schemas AS s on p.schema_id = s.schema_id
 在哪里p.is_ms_shipped = 0和s.schema_id = 9
-- Get list of possibly unused SPs (SQL 2008 only)
SELECT p.name AS 'SP Name',s.name -- Get list of all SPs in the current database
FROM sys.procedures AS p
inner join sys.schemas AS s on p.schema_id = s.schema_id
WHERE p.is_ms_shipped = 0 and s.schema_id = 9
EXCEPT
SELECT p.name AS 'SP Name' ,s.name -- Get list of all SPs from the current database
FROM sys.procedures AS p -- that are in the procedure cache
INNER JOIN sys.dm_exec_procedure_stats AS qs ON p.object_id = qs.object_id
inner join sys.schemas AS s on p.schema_id = s.schema_id
WHERE p.is_ms_shipped = 0 and s.schema_id = 9
请帮助我。
推荐答案
检查此链接：它有多种方法可以检查它：
Check this link: It has multiple ways to check it:
https://stackoverflow.com/ questions / 595742 / last-run-date-on-a-stored-procedure-in-sql-server
从跟踪到内置视图让我们 知道它是怎么回事。
from trace to inbuilt views let us know how it goes.
这篇关于如何查找过去3个月内未使用的存储过程的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
