Tuesday, December 8, 2009

SQL Server - View Formula for Computed Column

One of our SQL Server database tables has a computed column. When it was created it was given a definition like:

ALTER TABLE blahblah
ADD choose_value AS (CASE WHEN... ELSE... END) PERSISTED NOT NULL
/* details omitted */

and that's all fine, it follows the logic just fine and uses the value we want in that column. Pretty cool, really. But definitions change and things get updated. So I wanted to see if the formula definition that was currently in used in that table is actually the correct one.

The database tool has a neat feature that dumps the CREATE script for a table. However, it wasn't showing me the formula. It was just showing "choose_value" varchar(50) NOT NULL, which really wasn't helpful.

I found that to view the formula, a select from a system table was needed:

select definition
from sys.computed_columns
where name = 'choose_value'

0 comments:

Post a Comment