So I’ve been building an auditing system for my new employer that will help track all the changes to the data. Right now, some changes could be made, and we wouldn’t be able to tell who made the change, or when the change was made. I had an “Ah-ha” moment pretty early on.
“Let’s use Change Data Capture when we move to 2008R2 later this month!”
As soon as I went to grab the document to help explain what it is and how it works, I saw the unfortunate news, “Change data capture is available only on the Enterprise, Developer, and Evaluation editions of SQL Server.” You could hear the music from The Price is Right, when the yodeler falls off the mountain.
So I started looking into how the logging the Microsoft team worked. Based on what I found I built my system. While I know mine would have more overhead, it was very slim. Even considering I’m using triggers. The average insert time increased less than a hundredth of a second. I was pleased.
I built a script that would read the structure of the table and only audit the columns that weren’t a member of the Primary Key, and not one of the auditing columns (created by, last modified by, etc.) It worked without a hitch, until I hit a computed column.
I thought, “wait, I don’t want to audit that. The user has no way to change the value directly.” So I wanted to exclude computed columns. Normally I’d check
SELECT name FROM syscolumns WHERE isComputed != 1
But I’ve moved up to using INFORMATION_SCHEMA.COLUMNS. I looked through the documentation on the Information Schema Views. I couldn’t find where the isComputed column had moved to. So I started digging.
After a few minutes, I finally stumbled across a forum with this gem:
select * FROM INFORMATION_SCHEMA.COLUMNS where COLUMNPROPERTY(OBJECT_ID(table_name) ,COLUMN_NAME,'IsComputed') = 1
It shows computed columns, so I just reversed the logic to exclude computed columns and I was done.
That’ll do Google. That’ll do.