Get updated table list between particular duration


Hi,

Here is a table valued function to get updated table list between particular two datetime in sql database.


CREATE FUNCTION [dbo].[GetLastUpdateTablesDetail](@FromDate datetime,@ToDate datetime)
RETURNS @trackingItems TABLE (
ID    int    identity NOT NULL,
TableName    varchar(100)    NOT NULL,
LastUpdateDate    datetime    NOT NULL,
LastScanDate datetime    NULL
)
AS
BEGIN
set @FromDate =  DATEADD(MINUTE, -1, CONVERT(VARCHAR(10), @FromDate, 101))
set @ToDate = DATEADD(DAY, 1, CONVERT(VARCHAR(10), @ToDate, 101))

INSERT INTO @trackingItems (TableName, LastUpdateDate,LastScanDate)
SELECT OBJECT_NAME(OBJECT_ID) AS TableName,
last_user_update,
last_system_scan
FROM sys.dm_db_index_usage_stats
WHERE
database_id = DB_ID(DB_NAME())    AND
OBJECT_NAME(OBJECT_ID) in (select Name from sys.tables)
AND sys.dm_db_index_usage_stats.last_user_update >= @FromDate and sys.dm_db_index_usage_stats.last_user_update <= @ToDate
order by sys.dm_db_index_usage_stats.last_user_update desc

RETURN;
END;

then run this function like

 


SELECT * FROM  [dbo].[GetLastUpdateTablesDetail]('2013/10/01','2013/10/02')

You will get output like below  snapshot,You will get table name with updated date and time.

GetLastUpdateTablesDetail

 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: