How to check last table which was updated after a transaction in MS SQL

Question

0.00/5 (No votes)

See more:

Hai All,

I want to know which table got updated after doing a transaction from front end in MS SQL.

I checked the below code..
but didn't meeting my expectations.

I want the table in which data got inserted after doing a transaction.

please help me

What I have tried:

SELECT deqs.last_execution_time AS [Time], dest.TEXT AS [Query], dest.dbid
FROM sys.dm_exec_query_stats AS deqs
CROSS APPLY sys.dm_exec_sql_text(deqs.sql_handle) AS dest
ORDER BY deqs.last_execution_time DESC

Posted 1-Mar-17 21:44pm

shabadiveda

Updated 1-Mar-17 23:54pm

Add a Solution

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Vijai Anand.G · Answer 1 · 2017-03-01T23:54:00

Hope this will help you.
This Solution will help for less tables

1.Create a Table with following columns

TableName : LastUpdateTableInfo

1.Id - primary key(Auto generate)
2.TableName
3.UpdateDate

2.Create the AfterUpdate Trigger for each tables

use [DatabaseName] 
Go
create trigger trgupdatetableInfo on [TableName]
for update
as
declare @tableName as varchar(50)
declare @lastUpdateTime as varchar(50)

set @tableName =[yourtablename]
set @lastUpdateTime= GETDATE()

insert into dbo.LastUpdateTableInfo (tablename,UpdateDate) values (@tableName,@lastUpdateTime)
Go

Now whenever update happens the mention table will log the info .

You can get the last update table name by supply the following Query

SELECT  [Id]
      ,[TableName]
      ,[UpdateDate]
  FROM [placeDB].[dbo].[LastUpdateTableInfo] order by Id desc

Daniel Jones · Answer 2 · 2017-03-02T17:55:00

Solution 2

Take a look at this link, hope this will help you: SQL SERVER - Find Last Date Time Updated for Any Table - Journey to SQL Authority with Pinal Dave[^]

Posted 2-Mar-17 17:55pm

Daniel Jones

Updated 2-Mar-17 18:50pm