SQL Server - How to know which tables are used and which are not?
SELECT t.name AS [TableName],
       fi.page_count AS [Pages],
       fi.record_count AS [Rows],
       CAST(fi.avg_record_size_in_bytes AS int) AS [AverageRecordBytes],
       CAST(fi.avg_fragmentation_in_percent AS int) AS [AverageFragmentationPercent],
       SUM(iop.leaf_insert_count) AS [Inserts],
       SUM(iop.leaf_delete_count) AS [Deletes],
       SUM(iop.leaf_update_count) AS [Updates],
       SUM(iop.row_lock_count) AS [RowLocks],
       SUM(iop.page_lock_count) AS [PageLocks]
  FROM sys.dm_db_index_operational_stats(DB_ID(),NULL,NULL,NULL) AS iop
  JOIN sys.indexes AS i ON iop.index_id = i.index_id AND
                           iop.object_id = i.object_id
  JOIN sys.tables AS t ON i.object_id = t.object_id AND
                          i.type_desc IN ('CLUSTERED', 'HEAP')
  JOIN sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'SAMPLED') AS fi ON fi.object_id=CAST(t.object_id AS int) AND
                                                                                     fi.index_id=CAST(i.index_id AS int)
  GROUP BY t.name, fi.page_count, fi.record_count, fi.avg_record_size_in_bytes, fi.avg_fragmentation_in_percent
  ORDER BY [RowLocks] desc


Remarks:
sys.dm_db_index_operational_stats


  (How the Counters in the Metadata Cache Are Reset
  
  The data returned by sys.dm_db_index_operational_stats exists only as
  long as the metadata cache object that represents the heap or index is
  available. This data is neither persistent nor transactionally
  consistent. This means you cannot use these counters to determine
  whether an index has been used or not, or when the index was last
  used. For information about this, see sys.dm_db_index_usage_stats. 
  
  The values for each column are set to zero whenever the metadata for
  the heap or index is brought into the metadata cache and statistics
  are accumulated until the cache object is removed from the metadata
  cache. Therefore, an active heap or index will likely always have its
  metadata in the cache, and the cumulative counts may reflect activity
  since the instance of SQL Server was last started. The metadata for a
  less active heap or index will move in and out of the cache as it is
  used. As a result, it may or may not have values available. Dropping
  an index will cause the corresponding statistics to be removed from
  memory and no longer be reported by the function. Other DDL operations
  against the index may cause the value of the statistics to be reset to
  zero.)