How to identify the row in which any of the column was updated?

Question

Post reply

How to identify the row in which any of the column was updated?

Prasune Verma

SSC-Addicted

Points: 453
More actions
May 8, 2018 at 1:24 pm

#330799

Hi,
I am working on a scenario where I need to identify the rows in a table in which any of the column value was updated.
I have a table with 20 columns (1 of them is primary key). Now, out of those remaining 19 columns, if any of the column value was changed for any row, I need that row.
There is no column in the table with date values like update datetime.
Please help.
Thanks,
Prasune

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

I would start with looking into Change Tracking and see if that meets your needs.

Prasune Verma SSC-Addicted Points: 453 More actions · Answer 2

Thanks for your reply on my question Lynn. However, I have got only read only rights on the database. Cant alter the definitions.

Is there a way I can achieve my goal by writing a query?

Thanks,

Prasune

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 3

Based on the new information, not that I am aware. Perhaps you should be talking with your DBA to find ways to achieve your goals.

Phil Parkin SSC Guru Points: 246695 More actions · Answer 4

Aside from change tracking, you can do this only if you have two tables, one containing 'old' data and one with the current data.
But if all you have is the current data, and this is not a temporal table, and there is no history or audit table , I agree with Lynn.

If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

sgmunson SSC Guru Points: 110639 More actions · Answer 5

Basically, what it boils down to is that SQL Server does not hold on to "old" data unless you explicitly do something to keep track of it, such as create a history or audit table and populate it via trigger, or enable CDC. There's nothing you can do to see changes made before any such actions have been implemented. So for changes that have already been made, unless the DBAs know of an audit or history table that you don't have access to, you're out of luck.

Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)