The view sys.dm_exec_cached_plans and function dm_exec_sql_text can be used for this purpose.
Let us execute the following query
select top 10
db_name(sql_text.dbid) as db_name,
sys.dm_exec_cached_plans as cache_plan
cross apply sys.dm_exec_sql_text(plan_handle) as sql_text
Here are some important points to note about this query:
- The result is based on the descending order of the column ‘usecounts’ which is used to indicate the number of times the cached objects are used.
- The column ‘object type’ indicates whether the statement is of the type procedure,view, adhoc etc.
- The column text shows the exact statement which was cached.
- The column db_name is null for the cached types adhoc, prepared, etc