Sign in
 
Home20122008 R2200820052000LibraryForums
SQL Server >
> 
> 
To Display the list of tables that have been enabled for SQL Cache

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

  • Monday, February 13, 2012 12:06 PM
     
     
    Sign In to Vote
    0

    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

     

All Replies

  • Monday, February 13, 2012 5:02 PM
     
     Answered Has Code
    Sign In to Vote
    0

    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