minimize number of tables for syncronyzation

AlexG7

It is more like wish list   for data compare option

filter tables that need synchronization

WITH LastActivity (ObjectID, LastAction) AS 

(

SELECT object_id AS TableName, last_user_seek as LastAction

FROM sys.dm_db_index_usage_stats u (NOLOCK)

WHERE database_id = db_id(db_name())

UNION 

SELECT object_id AS TableName, last_user_scan as LastAction

FROM sys.dm_db_index_usage_stats u (NOLOCK)

WHERE database_id = db_id(db_name())

UNION

SELECT object_id AS TableName, last_user_lookup as LastAction

FROM sys.dm_db_index_usage_stats u (NOLOCK)

WHERE database_id = db_id(db_name())

UNION

SELECT object_id AS TableName, last_user_update as LastAction

FROM sys.dm_db_index_usage_stats u (NOLOCK)

WHERE database_id = db_id(db_name())

)

SELECT OBJECT_NAME(so.object_id) AS TableName, MIN(la.LastAction) as LastSelect

FROM sys.objects so (NOLOCK)

LEFT JOIN LastActivity la (NOLOCK) on so.object_id = la.ObjectID

WHERE so.type = 'U'

AND so.object_id > 100

AND so.is_ms_shipped < 1

GROUP BY OBJECT_NAME(so.object_id)

having MIN(la.LastAction) > '2018-01-01'  --eliminate null add date before last reboot

ORDER BY OBJECT_NAME(so.object_id) 

original code taken from (https://www.sqlservercentral.com/Forums/1868796/sysdmdbindexusagestats-what-about-tables-w-NO-INDEXES-defined )

sam.blackburn

It sounds like you're looking for a "last accessed date" for each table, similar to the last schema modification date feature released in SQL Compare 12.  That work was done in response to a UserVoice request, although I can't see one for this feature.  It would be good to gauge whether many people want this kind of feature, as well as what exactly they want (e.g. last read versus last write time).