SQL Server/T-SQL Tutorial/System Tables Views/sys.syscacheobjects

Query sys.syscacheobjects

4> SELECT top 10 cacheobjtype, objtype, usecounts, sql
5> FROM sys.syscacheobjects
6> WHERE sql NOT LIKE "%cache%"
7>   AND sql NOT LIKE "%sys.%"
8>   AND sql NOT LIKE "%sp_executesql%";
9>
10> GO