sp_helpcache provides information about a cache and the entities bound to it when you provide the cache name:
sp_helpcache pubs_cache
Cache Name Config Size Run Size Overhead -------------------- ------------- ---------- ---------- pubs_cache 10.50 Mb 10.50 Mb 0.56 Mb ------------------ Cache Binding Information: ------------------ Cache Name Entity Name Type Index Name Status ---------- ----------- ---- ---------- ------ pubs_cache pubs2.dbo.titles index titleind V pubs_cache pubs2.dbo.au_pix index tau_pix V pubs_cache pubs2.dbo.titles table V pubs_cache pubs2.fred.sales_east table V
If you use sp_helpcache without a cache name, it prints information about all the configured caches on Adaptive Server and all the objects that are bound to them.
sp_helpcache performs string matching on the cache name, using %cachename%. For example, “pubs” matches both “pubs_cache” and “pubs_log”.
The “Status” column reports whether a cache binding is valid (“V”) or invalid (“I”). If a database or object is bound to a cache, and the cache is deleted, binding information is retained in the system tables, but the cache binding is marked as invalid. All objects with invalid bindings use the default data cache. If you subsequently create another cache with the same name, the binding becomes valid when the cache is activated by a restart of Adaptive Server.
