Retrieve information about which Tables/Procedures/Views are most accessed in SQL SERVER

Question

select 
    db_name(database_id) as banco, 
    object_name(object_id) as objeto, 
    stats.user_seeks,
    stats.user_scans,
    stats.user_lookups,
    stats.user_updates
from 
    sys.dm_db_index_usage_stats as stats

I have this SELECT that brings me information grouped by the bank, I thought the object referred to a table, view, stored Procedure or Trigger, but I did not get the result I wanted.

There is a SELECT that brings me this information?

Expected result:

database objeto  select data_ultimo_select update data_ultimo_update delete data_ultimo_delete
-------- ------- ------ ------------------ ------ ------------------ ------ ------------------
banco1   tabela1 1500   01-01-2013         500    01-01-2014         500    01-01-2015
banco2   tabela2 2500   01-01-2011         1500   01-01-2012         1500   01-01-2013

Is it possible?

I’ve searched several places and I haven’t found where SQL SERVER stores, if it does, this information.

Answer 1

Use the following function:

CREATE FUNCTIOn fn_TablesLastUpdateDate(@Date NVARCHAR(20))

RETURNS @table TABLE(TableName NVARCHAR(40), LastUpdated Datetime)

AS

BEGIN

    IF(@Date='') OR (@Date Is Null) OR (@Date='0')

        BEGIN
            INSERT INTO @table
            SELECT TOP 100 PERCENT TABLENAME, LASTUPDATED FROM 
            (
                SELECT  B.NAME AS 'TABLENAME', MAX(STATS_DATE (ID,INDID)) AS LASTUPDATED
                FROM    SYS.SYSINDEXES AS A
                        INNER JOIN SYS.OBJECTS AS B ON A.ID = B.OBJECT_ID
                WHERE   B.TYPE = 'U'  AND STATS_DATE (ID,INDID) IS NOT NULL 
                GROUP BY B.NAME
            ) AS A
            ORDER BY LASTUPDATED DESC
        END
    ELSE

        BEGIN
            INSERT INTO @table
            SELECT TOP 100 PERCENT TABLENAME,LASTUPDATED FROM 
            (
                SELECT  B.NAME AS 'TABLENAME', MAX(STATS_DATE (ID,INDID)) AS LASTUPDATED,
                        CONVERT(VARCHAR, MAX(STATS_DATE (ID,INDID)), 103) as Date
                FROM    SYS.SYSINDEXES AS A
                        INNER JOIN SYS.OBJECTS AS B ON A.ID = B.OBJECT_ID
                WHERE   B.TYPE = 'U'  AND STATS_DATE (ID,INDID) IS NOT NULL 
                GROUP BY B.NAME
            ) AS A
            WHERE Date=@Date
            ORDER BY LASTUPDATED DESC
        END
    RETURN

END

Use:

SELECT * from fn_TablesLastUpdateDate('14/01/2015')

This is the accepted answer to this question.