I often get asked “how do I find the contents of the SQL Server Plan Cache?” My first response is just use the Database Health Reports and you can get this through a nice user interface. But if you still want to get at the Plan Cache contents, I use the following query.
SELECT UseCounts, RefCounts, CacheObjtype, ObjType, DB_NAME(dbid) as DatabaseName, SQL, sqlbytes FROM sys.syscacheobjects WHERE dbid = db_id();
Again, the Database Health Reportsgives you a much better UI presentation of the conents of the plan cache.
Enjoy the query and please try out the Database Health Reports.