As part of some overall auditing in one of my projects, we recently added a LastUpdatedByUserID column to all of the major tables. As the name implies, this column stores the ID of the user who last updated the row. In order to keep an audit trail of the changes, for each table in the database, we have a corresponding TableName_Audit table that is updated (by triggers on the main table) every time the main table is modified.
I noticed that for one of the tables that had had the LastUpdatedByUserID column added, the script to regenerate the audit table hadn’t been run, so the audit table was missing the LastUpdatedByUserID column. This would entirely nullify the point of the column.
This led me to wonder if there were any other tables in the same state. rather than check this manually, I decided to write a script to do it, as this would be useful for future tables. Being a Linq type of person (I rarely write SQL any more), I decided to see if I could do this in LinqPad.
It took a bit of fiddling, but the end result was what I wanted, and paved the way for future queries.
Leave a Comment