USE master
GO

-- ----------------------------------------------
-- Create AuidtTable if it does not exist
-- ----------------------------------------------

IF Object_id('AuditTable') IS NULL
  CREATE TABLE AuditTable
    (
       DatabaseName     VARCHAR(500),
       last_access_time DATETIME,
       no_of_days       INT,
	   last_upd_dtm		DATETIME
    );
GO

-- ----------------------------------------------
-- Insert entries from all DB's.
-- ----------------------------------------------

INSERT master..AuditTable
SELECT name,
       Getdate(),
       0,
       Getdate()
FROM   sys.databases
WHERE  name NOT IN ( 'master', 'tempdb', 'model', 'msdb' ) ;
GO
-- ----------------------------------------------
-- Update audit table for the first time
-- ----------------------------------------------

WITH
Base
     AS (SELECT Db_name(database_id) DatabaseName,
                last_user_seek       AS last_access_time
         FROM   sys.dm_db_index_usage_stats
         UNION ALL
         SELECT Db_name(database_id) DatabaseName,
                last_user_scan
         FROM   sys.dm_db_index_usage_stats
         UNION ALL
         SELECT Db_name(database_id) DatabaseName,
                last_user_lookup
         FROM   sys.dm_db_index_usage_stats
         UNION ALL
         SELECT Db_name(database_id) DatabaseName,
                last_user_update
         FROM   sys.dm_db_index_usage_stats),
AccessInfo
     AS (SELECT DatabaseName,
                Max(last_access_time) last_access_time,
                Datediff(dd, Max(last_access_time), Getdate()) AS no_of_days
         FROM   Base
         GROUP  BY DatabaseName
         HAVING DatabaseName NOT IN ('master', 'tempdb', 'model', 'msdb'))
MERGE master..AuditTable AS tgt
USING (SELECT DatabaseName,last_access_time,no_of_days,getdate() last_upd_dtm FROM   AccessInfo) AS src
ON tgt.databasename = src.databasename
WHEN MATCHED 
THEN 
    UPDATE SET tgt.last_access_time = src.last_access_time, tgt.no_of_days = src.no_of_days, tgt.last_upd_dtm = src.last_upd_dtm
WHEN NOT MATCHED BY TARGET 
THEN 
    INSERT (DatabaseName,last_access_time,no_of_days,last_upd_dtm) 
    VALUES ( src.DatabaseName,src.last_access_time,src.no_of_days,src.last_upd_dtm);
GO

	Select db_name(database_id),Max(x.Last_Date) Last_Access_time
	From 
		sys.dm_db_index_usage_stats
		CROSS APPLY(VALUES(last_user_update),(last_user_lookup),(last_user_scan),(last_user_seek)) x (last_date)
	Where last_date is NOT NULL
		AND db_name(database_id) not in ('msdb','master','tempdb')
	group by database_id

;With Cte_Src(
	Select 
		db_name(database_id) databasename
		,Max(x.Last_Date) Last_Access_time
		,GETDATE() curr_time
	From 
		sys.dm_db_index_usage_stats
		CROSS APPLY(VALUES(last_user_update)
					,(last_user_lookup)
					,(last_user_scan)
					,(last_user_seek)) x (last_date)
	Where last_date is NOT NULL
		AND db_name(database_id) not in ('msdb','master','tempdb')
	group by database_id
)	
MERGE master..AuditTable AS tgt
USING Cte_Src AS src
ON tgt.databasename = src.databasename
WHEN MATCHED AND src.Last_Access_time != tgt.Last_Access_time
THEN 
    UPDATE SET 
		tgt.last_access_time = src.last_access_time
		, tgt.no_of_days = DateDiff(d,src.last_access_time, src.curr_time)
		, tgt.last_upd_dtm = src.curr_time
WHEN NOT MATCHED BY TARGET 
THEN 
    INSERT (DatabaseName,last_access_time,no_of_days,last_upd_dtm) 
    VALUES ( src.DatabaseName,src.last_access_time,DateDiff(d,src.last_access_time, src.curr_time));