While discussing with one of my clients, this question raised but I knew that there is no direct way unless we use something related to indexes. So the query is simple, all we have to do is, get the total count of all scans, seeks and lookups for particular indexes;
SELECT t.name AS TableName, SUM(u.user_seeks + u.user_scans + u.user_lookups) AS NumberOfTimeAccessed FROM sys.dm_db_index_usage_stats u INNER JOIN sys.tables t ON t.object_id = u.object_id WHERE database_id = DB_ID() GROUP BY t.name ORDER BY SUM(u.user_seeks + u.user_scans + u.user_lookups) DESC
Is this 100% accurate? As per my understanding, this is not 100% accurate. You might get larger numbers when you have multiple indexes but if you all want is to know which tables get more hit, this does a reasonable job.
Does this capture the tables that are based on heap structure? Yes of course, still you see the count on heap tables too.