How can we help you today? How can we help you today?

minimize number of tables for syncronyzation

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


AlexG7
0

Comments

1 comment

Sort by
  • 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).
    sam.blackburn
    0

Add comment

Please sign in to leave a comment.