I would like to know how to get the list of tables that have been enabled for SQL Cache Dependency using Broker Service. Please help me to find out. If you have some dmv's or queries that will be the great help.
As per BooksOnLine you could use the following query (based on sys.dm_qn_subscriptions DMV)
SELECT qn.id AS query_subscription_id
,it.name AS internal_table_name
,it.object_id AS internal_table_id
FROM sys.internal_tables AS it
JOIN sys.dm_qn_subscriptions AS qn ON it.object_id = qn.object_id
WHERE it.internal_type_desc = 'QUERY_NOTIFICATION';
Marked as answer byStephanie LvTuesday, February 21, 2012 7:39 AM
Microsoft is conducting an online survey to understand your opinion of the Technet Web site. If you choose to participate, the online survey will be presented to you when you leave the Technet Web site.