minimize number of tables for syncronyzation

AlexG7AlexG7 Posts: 3 New member
edited October 30, 2018 6:35PM in SQL Data Compare
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


Tagged:

Best Answer

  • sam.blackburnsam.blackburn Posts: 212 Gold 1
    Accepted Answer
    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).
    Software Developer
    Redgate Software
Sign In or Register to comment.