sp_prepexec (sp_execute) vs. sp_executeSQL
Are actual stored procedures the only mechanism that implements temp table caching or do system stored procedures such as sp_executeSQL / sp_execute also take advantage of them?


You need a real stored procedure (CREATE PROCEDURE) to benefit from temporary table caching. This includes temporary stored procedures (#procname).


  Point #3 on this blog post suggests that EXEC cannot use temp table caching, but leaves out whether sp_executeSQL can.


Notice that EXECUTE is used to run sp_executesql.

Testing: There are many ways to check whether caching is occurring. Some of them are listed in my original article referenced in the question, some more methods are shown in my follow-up post, Temporary Table Caching Explained, for example:

SELECT 
    DOMCC.name,
    DOMCC.pages_kb,
    DOMCC.pages_in_use_kb,
    DOMCC.entries_count,
    DOMCC.entries_in_use_count
FROM sys.dm_os_memory_cache_counters AS DOMCC
WHERE DOMCC.[type] = N'CACHESTORE_TEMPTABLES';


Stored procedure input TVPs are also cached, and starting with SQL Server 2012, these can also be cached when used with sp_executesql. See the linked CSS blog post for details.