Purpose:
To find how many times objects of some type of SQL objects were executed.
Solution:
This script refer to views - but you can change it to any other type by replacing the call from sys.views to the other relevant sys table.
; WITH QueryExecutions AS
(
SELECT
QueryText =
SUBSTRING
(
SQLTexts.text ,
QueryStats.statement_start_offset / 2 ,
(
CASE QueryStats.statement_end_offset
WHEN -1 THEN LEN (SQLTexts.text)
ELSE QueryStats.statement_end_offset / 2
END
- QueryStats.statement_start_offset / 2
)
+ 1
) ,
ExecutionCount = QueryStats.execution_count
FROM
sys.dm_exec_query_stats AS QueryStats
CROSS APPLY
sys.dm_exec_sql_text (QueryStats.sql_handle) AS SQLTexts
)
SELECT
ViewSchema = SCHEMA_NAME (Views.schema_id) ,
ViewName = Views.name ,
UsageCount = SUM (ISNULL (QueryExecutions.ExecutionCount , 0))
FROM sys.views AS Views
LEFT OUTER JOIN
QueryExecutions
ON QueryExecutions.QueryText LIKE N'%[. ' + CHAR(9) + CHAR(10) + CHAR(13) + N'\[]' +
Views.name + N'[ ;' +
CHAR(9) + CHAR(10) + CHAR(13) + '\]]%'
ESCAPE N'\' COLLATE DATABASE_DEFAULT
GROUP BY SCHEMA_NAME (Views.schema_id) , Views.name
ORDER BY UsageCount DESC, ViewSchema ASC, ViewName ASC
GO
