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 to 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.
-- by Kalen Delaney, 2012
-- Feel free to remove those filters from the WHERE clause at the bottom
IF EXISTS (SELECT 1 FROM sys.views WHERE name = 'sp_cacheobjects')
DROP VIEW sp_cacheobjects;
CREATE VIEW sp_cacheobjects(bucketid, cacheobjtype, objtype, objid, dbid, dbidexec, uid, refcounts,
usecounts, pagesused, setopts, langid, date_first, dateformat, status, lasttime, maxexectime, avgexectime, lastreads,
lastwrites, sqlbytes, sql)
SELECT pvt.bucketid, CONVERT(nvarchar(19), pvt.cacheobjtype) as cacheobjtype, pvt.objtype,
CONVERT(int, pvt.objectid)as object_id, CONVERT(smallint, pvt.dbid) as dbid,
CONVERT(smallint, pvt.dbid_execute) as execute_dbid,
CONVERT(smallint, pvt.user_id) as user_id,
pvt.refcounts, pvt.usecounts, pvt.size_in_bytes / 8192 as size_in_bytes,
CONVERT(int, pvt.set_options) as setopts, CONVERT(smallint, pvt.language_id) as langid,
CONVERT(smallint, pvt.date_format) as date_format, CONVERT(int, pvt.status) as status,
CONVERT(bigint, 0), CONVERT(bigint, 0), CONVERT(bigint, 0),
CONVERT(bigint, 0), CONVERT(bigint, 0),
CONVERT(int, LEN(CONVERT(nvarchar(max), fgs.text)) * 2), CONVERT(nvarchar(3900), fgs.text)
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;
I hope you find this useful!