In SQL Server, how do you find un-used stored procedures and un-used views?
There is no in-built mechanism to identify the un-used stored procedures. When an stored procedure is called, an entry will be created in "sys.dmexecquery_stats". Based on this information, we can find un-used stored procedures by using below query. However, this information will be deleted once the server re-starts.
SELECT * from sys.procedures P
LEFT OUTER JOIN
FROM sys.dm_exec_query_stats AS s1
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS x
OBJECTPROPERTYEX(x.objectid,''IsProcedure'') = 1
)T on P.object_id = T.objectid
WHERE T.objectid is null
For views, it will store the execution plan in DMVs. Below query can be used to get un-used views. When the server is re-started, this information will be cleared. Also, when the server has more run out of procedure cache, it will dump the old execution plans. so, this query result might not be approximate.
SELECT v.name [ViewName]
FROM sys.views v
WHERE v.is_ms_shipped = 0
EXCEPT SELECT o.Name [ViewName]
FROM master.sys.dm_exec_cached_plans p
CROSS APPLY sys.dm_exec_sql_text(p.plan_handle) t
INNER JOIN sys.objects o ON t.objectid = o.object_id [here]
(Above script taken from Michael K. Campbell article here)
SQL Server Interview Questions · · · ·