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.

Tags:

2 Responses to “Checking for Computed Columns”

  1. social workers 20110120 at 13:01 #

    What a great resource!

  2. deb 20130918 at 14:41 #

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

Leave a Reply

Notify me of followup comments via e-mail. You can also subscribe without commenting.