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

Navigation
5
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 which brings me information grouped by the database, I thought the object referred to a table, view, stored procedure or trigger, but I did not get the result I wanted.

Is there 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 have already researched in several places and I have not found where SQL SERVER saves this information, if it saves it.

    
asked by anonymous 13.01.2015 / 13:58

1 answer

2

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

Usage: 

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

This is the accepted answer of this question .

    
14.01.2015 / 13:45
Chrome and bug background fixed What is the benefit of Jsf's life cycle?