Checking for Computed Columns

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.

By Shannon Lowder

Shannon Lowder is the Database Engineer you've been looking for! Look no further for expertise in: Business Analysis to gather the business requirements for the database; Database Architecting to design the logical design of the database; Database Development to actually build the objects needed by the business logic; finally, Database Administration to keep the database running in top form, and making sure there is a disaster recovery plan.

2 comments

  1. Thanks for the info. Just what I was looking for. And for the reference to Babe! I can’t stop laughing.

Leave a comment

Your email address will not be published. Required fields are marked *