none
To Display the list of tables that have been enabled for SQL Cache

    Question

  • Dear Experts,

    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.

    Thank You,

    Guru

    Monday, February 13, 2012 12:06 PM

Answers

  • 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';
    GO


    • Marked as answer by Stephanie Lv Tuesday, February 21, 2012 7:39 AM
    Monday, February 13, 2012 5:02 PM