Geek City: sp_cacheobjects for SQL Server 2012

In a post about 4 1/2 years ago, I gave you my version of a replacement for the old pre-2005 pseudotable syscacheobjects. I called it sp_cacheobjects and created it as a view in the master database. With the sp_ prefix, the view can be accessed from any database.

When testing this on SQL Server 2012, I noticed that I almost always got a lot more rows back than I was expecting. Even when I added a WHERE clause to limit the database to only the database I was working in, I STILL got way too many rows back. It turns out that in SQL Server 2012, SQL Server is frequently running background queries checking for both filetable and fulltext activites, and these background queries, that run in all databases, are cached like any other queries. To get the same kinds of results from this view that I got in SQL Server 2008, I needed to add a few extra filters, so I am including the modified version of my view here.

-- Create a view to show most of the same information as SQL Server 2000's syscacheobjects -- This script has been updated for SQL Server 2012 to remove plans dealing with filetable and fulltext activities, -- and to also not return queries from system databases.

FROM (SELECT ecp.*, epa.attribute, epa.value FROM sys.dm_exec_cached_plans ecp OUTER APPLY sys.dm_exec_plan_attributes(ecp.plan_handle) epa) as ecpa PIVOT (MAX(ecpa.value) for ecpa.attribute IN ([set_options],[objectid],[dbid], [dbid_execute],[user_id],[language_id],[date_format],[status])) as pvt OUTER APPLY sys.dm_exec_sql_text(pvt.plan_handle) fgs WHERE cacheobjtype like 'Compiled%' AND text NOT LIKE '%filetable%' AND text NOT LIKE '%fulltext%' AND pvt.dbid between 5 and 32766;