Table usage
-
Does anyone have, or know of a script where you can view the last time a table/database was used?
-
Do you mean queried (Read only)? There's no way to do this other than use Profiler to track access. For writes, it could be done with triggers.
I guess you could set a DDL trigger on the user being activated in some way with 2005.
-
If you want to know when the table's schema has been altered, you can look at the modify_date column of sys.objects.
This works retrospectively.
If you only query the data in a table, this is not persisted, unless you run a trace. In a retrospective way you cannot do this.
If you modify the data, you can check what the modification is using third party tools that analyze the transaction log. This works retrospectively.
Regards,
Andras
Andras Belokosztolszki, MCPD, PhD
GoldenGate Software -
Thanks to both of you for your replys, i was after something which told me if data had been read, or modified but it seems SQL doesnt provide this, not to worry
I think i will just run a trace for a few days and see if anyone logs in to the database at all.
John
-
Actually you sort of can. If the table is clustered the index has to be hit. If not, this will only show index access. FYI, because this is a dynamic view it is only since the last restart.
SELECT
database_id,OBJECT_ID, MAX(last_scan) most_recent_access
from
(
SELECT
database_id,OBJECT_ID,last_user_lookup,last_user_scan,last_user_seek,last_user_update
FROM sys.dm_db_index_usage_stats) AS p
UNPIVOT
(last_scan FOR scan_type IN
last_user_lookup,last_user_scan,last_user_seek,last_user_update)
)AS unpvt
GROUP BY database_id,OBJECT_ID
go
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply