CODEWITHSUNDEEP
sql-serversql-server-2008
rbasniak
rbasniak

Reputation: 4984

Getting last user update time for all tables in a database

In SQL SERVER 2008 R2 I have 7 databases, each one with around 1000 tables.

I need to know wich tables were last updated by users. With some research I got to this code:

SELECT  OBJECT_NAME(OBJECT_ID) AS TableName,
                                  last_user_update,
                                  user_updates,
                                  index_id
FROM sys.dm_db_index_usage_stats
WHERE database_id = DB_ID('SP3D_DB_RESEARCH_MDB') AND 
                    OBJECT_ID = OBJECT_ID('CORECone3d')
GO

This returns only one result, since it's asking for last user updates on a specific table/database.

Is there any way to put it in some sort of loop so it iterate through all tables in a database and I can get which tables were last modified?

It does not have to be a query as result, it could be a simple text output.

I don't know if this matters, but I'm using MS SQL SERVER MANAGEMENT STUDIO.

Based on Pradeep answer I get the following results, with TableName NULL

query results based on answer

Upvotes: 0

Views: 4524

Answers (1)

Pரதீப்
Pரதீப்

Reputation: 93734

Try this:

SELECT  OBJECT_NAME(OBJECT_ID) AS TableName,
                              last_user_update,
                              user_updates,
                              index_id
FROM sys.dm_db_index_usage_stats
WHERE database_id = DB_ID('SP3D_DB_RESEARCH_MDB')

Upvotes: 1

Related Questions