none
T-SQL query to Get RowCount and When Last Table was Updated DateTime? RRS feed

  • Question

  • Query to Get RowCount and When Last Table was Updated DateTime

    IF OBJECT_ID('tempdb.dbo.#TableLastUpdated', 'U') IS NOT NULL
     DROP TABLE #TableLastUpdated

    SELECT OBJECT_NAME(OBJECT_ID) AS TableName, 
           MAX(last_user_update) AS Last_User_Update
    INTO #TableLastUpdated
    FROM sys.dm_db_index_usage_stats
    GROUP BY OBJECT_NAME(OBJECT_ID)


    SELECT T.name AS TableName, 
           I.rows AS NumberOfRows,
       tlu.Last_User_Update

    FROM sys.tables AS T
         INNER JOIN sys.sysindexes AS I ON T.object_id = I.id
                                           AND I.indid < 2
         LEFT JOIN #TableLastUpdated tlu ON tlu.TableName = T.name
    ORDER BY Last_User_Update ASC

    Is there any other way to find out this..? Don't want to scan each and every table.


    Thanks Shiven:) If Answer is Helpful, Please Vote

    Wednesday, October 16, 2019 12:30 AM

All replies

  • Hi Shivendoo Kumar Dubey,

     

    >>Query to Get RowCount and When Last Table was Updated DateTime

     

    The T-SQL language provides us with the @@ROWCOUNT system variable. This variable returns the number of rows affected by the last executed statement in the batch.

     

    Would you please try to use following code to get SQL Server table last update time?

     

    SELECT 

        object_name(STAT.object_id) AS ObjectName,

        USER_SEEKS,

        USER_SCANS,

        USER_LOOKUPS,

        USER_UPDATES,

        LAST_USER_SEEK,

        LAST_USER_SCAN,

        LAST_USER_LOOKUP,

        LAST_USER_UPDATE

    FROM  SYS.DM_DB_INDEX_USAGE_STATS STAT JOIN

            SYS.TABLES TAB ON (TAB.OBJECT_ID = STAT.OBJECT_ID)

    WHERE   DATABASE_ID = DB_ID()

     

    Best regards,

    Dedmon Dai


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Wednesday, October 16, 2019 2:49 AM
  • Get Row count

    SELECT
    t.name,
    [RowCount] = SUM
    (
    CASE
    WHEN (p.index_id < 2) AND (a.type = 1) THEN p.rows
    ELSE 0
    END
    )
    FROM
    sys.tables t
    INNER JOIN sys.partitions p
    ON t.object_id = p.object_id
    INNER JOIN sys.allocation_units a
    ON p.partition_id = a.container_id

    WHERE t.name ='TBLNAME'

    GROUP BY
    t.name
    ORDER BY [RowCount] DESC

    ----Last Time updated

    Since SQL Server 2005 you can use the sys.dm_db_index_usage_stats data 
    management view, look at the column last_user_update, you can also see when 
    the table was last accessed (last_user_seek and last_user_scan - do a MAX on 
    them).

    SELECT
        last_user_seek = MAX(last_user_seek),
        last_user_scan = MAX(last_user_scan),
        last_user_lookup = MAX(last_user_lookup),
        last_user_update = MAX(last_user_update)
    FROM
        sys.dm_db_index_usage_stats
    WHERE
        [database_id] = DB_ID()
        -- if you want to leave out system objects, uncomment the next line:
        -- AND OBJECTPROPERTY(object_id, 'IsMsShipped') = 0


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Wednesday, October 16, 2019 7:26 AM
    Answerer