Skip to content

Checking for Computed Columns

2011 January 6
by Shannon Lowder

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

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:

 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.

2 Responses leave one →
  1. January 20, 2011

    What a great resource!

  2. deb permalink
    September 18, 2013

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

Leave a Reply

Note: You can use basic XHTML in your comments. Your email address will never be published.

Subscribe to this comment feed via RSS