Question

In SQL Server, how do you find un-used stored procedures and un-used views?

Answer

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
(
SELECT x.objectid
FROM sys.dm_exec_query_stats AS s1
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS x
WHERE
 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][1]

(Above script taken from Michael K. Campbell article here)

Read More..   [32134 clicks]

Published under: SQL Server Interview Questions ·  ·  ·  ·