Primary key changes

Question

Primary key changes

sdh96

Mr or Mrs. 500

Points: 596
More actions
November 11, 2015 at 1:55 pm

#322297

Is there a way to query the changes to a table's primary key changes such as date, user, etc?

Viewing 4 posts - 1 through 3 (of 3 total)

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

sgmunson SSC Guru Points: 110640 More actions · Answer 1

Are you talking about changes to values, or changes made to the schema that affect the primary key field? There is no inherent provision for tracking either one, so in order to know when a value has changed, you need to implement some kind of change tracking mechanism. CDC (aka Change Data Capture) is one method, although a trigger that inserts the changes into a tracking table can also work. Either way, it's a fair amount of work.

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

sdh96 Mr or Mrs. 500 Points: 596 More actions · Answer 2

I was looking for changes to the schema and not the values. But thanks for your reply.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

You can look in the default trace, it does track schema changes. However it'll only contain recent history. How recent depends on how active the server it. Could be hours, could be weeks.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass