Options
minimize number of tables for syncronyzation
AlexG7
Posts: 3 New member
It is more like wish list for data compare option
filter tables that need synchronization
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 )
original code taken from (https://www.sqlservercentral.com/Forums/1868796/sysdmdbindexusagestats-what-about-tables-w-NO-INDEXES-defined )
Tagged:
Best Answer
-
Optionssam.blackburn
Posts: 224 Gold 2
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
