Get Last Tables Access or Structure Changed on SQL Server

There are two facts of table that DBA want to check

1. For Tables structural change

IF OBJECT_ID('tempdb..#lastaccess1') IS NOT NULL

DROP TABLE #lastaccess1;

CREATE TABLE #lastaccess1(

    [DBName] sysname,

    [TableNames] NVARCHAR(256),

    [create_date] datetime,

    [modify_date] datetime,

   )

DECLARE @Statement1 VARCHAR(4000)

SET @Statement1 = 'USE [?]

SELECT  ''?'' as [DBNAME],  [TableName] = name,

create_date, modify_date

FROM    sys.tables

ORDER BY modify_date DESC'

INSERT INTO #lastaccess1([DBName] ,[TableNames] ,[create_date], [modify_date] )

EXEC sp_MSforeachdb @Statement1;

SELECT * FROM #lastaccess1 WHERE [DBNAME] not in ('master', 'msdb','model','tempdb');

DROP TABLE #lastaccess1;

 2. For Tables that were last updated or accessed

 DMV: sys.dm_db_index_usage_stat

IF OBJECT_ID('tempdb..#lastaccess2') IS NOT NULL

DROP TABLE #lastaccess2;

CREATE TABLE #lastaccess2(

    [DBName] sysname,

    [TableNames] NVARCHAR(256),

    [last_user_update] datetime,

    [last_user_seek] datetime,

    [last_user_scan] datetime,

    [last_user_lookup] datetime

)

DECLARE @Statement2 VARCHAR(4000)

SET @Statement2 = 'USE [?]

SELECT  ''?'' as [DBNAME],  [TableName] = OBJECT_NAME(object_id),

last_user_update, last_user_seek, last_user_scan, last_user_lookup

FROM    sys.dm_db_index_usage_stats

ORDER BY DBNAME, last_user_lookup DESC';

INSERT INTO #lastaccess2([DBName] ,[TableNames] ,[last_user_update], [last_user_seek] , [last_user_scan] , [last_user_lookup])

EXEC sp_MSforeachdb @Statement2;

--SELECT * FROM #lastaccess2 WHERE [DBNAME] not in ('master', 'msdb','model','tempdb');

SELECT * FROM #lastaccess2;

DROP TABLE #lastaccess2;

 ---------------------------------

the problems is you will get some "NULL" for Object_Name, it doesn't mean the objects are disappeared. It means that the Object_ID were refer to other Databases.

So you can change the script to the below

IF OBJECT_ID('tempdb..#lastaccess2') IS NOT NULL

DROP TABLE #lastaccess2;

CREATE TABLE #lastaccess2(

    [DBName] sysname,

    [TableNames] NVARCHAR(256),

    [last_user_update] datetime,

    [last_user_seek] datetime,

    [last_user_scan] datetime,

    [last_user_lookup] datetime

)

DECLARE @Statement2 VARCHAR(4000)

SET @Statement2 = 'USE [?]

SELECT  ''?'' as [DBNAME],  [TableName] = OBJECT_NAME(object_id,database_id),

last_user_update, last_user_seek, last_user_scan, last_user_lookup

FROM    sys.dm_db_index_usage_stats

ORDER BY DBNAME, last_user_lookup DESC';

INSERT INTO #lastaccess2([DBName] ,[TableNames] ,[last_user_update], [last_user_seek] , [last_user_scan] , [last_user_lookup])

EXEC sp_MSforeachdb @Statement2;

--SELECT * FROM #lastaccess2 WHERE [DBNAME] not in ('master', 'msdb','model','tempdb');

SELECT * FROM #lastaccess2;

DROP TABLE #lastaccess2;

 ---------------

I added databse_id within OBJECT_NAME function.

 ---------------

If I want to know the Schema?

IF OBJECT_ID('tempdb..#lastaccess3') IS NOT NULL

DROP TABLE #lastaccess3;

CREATE TABLE #lastaccess3(

    [DBName] sysname,

    [Schema] NVARCHAR(256),

    [TableNames] NVARCHAR(256),

    [last_user_update] datetime,

    [last_user_seek] datetime,

    [last_user_scan] datetime,

    [last_user_lookup] datetime

)

DECLARE @Statement3 VARCHAR(4000)

SET @Statement3 = 'USE [?]

SELECT  ''?'' as [DBNAME], [Schema]= OBJECT_SCHEMA_NAME(object_id,database_id), [TableName] = OBJECT_NAME(object_id,database_id),

last_user_update, last_user_seek, last_user_scan, last_user_lookup

FROM    sys.dm_db_index_usage_stats

ORDER BY DBNAME, last_user_lookup DESC';

INSERT INTO #lastaccess3([DBName] ,[Schema], [TableNames] ,[last_user_update], [last_user_seek] , [last_user_scan] , [last_user_lookup])

EXEC sp_MSforeachdb @Statement3;

--SELECT * FROM #lastaccess2 WHERE [DBNAME] not in ('master', 'msdb','model','tempdb');

SELECT * FROM #lastaccess3;

DROP TABLE #lastaccess3;

 -------------------

I added "OBJECT_SCHEMA_NAME(object_id,database_id)"

So simple, right?