14

I would like to query the definition of a computed column from the database, but can't find a command that seems to do what I want...

For instance, if a column is defined as: 

CallDT AS (CONVERT([datetime],dateadd(second,[StartDate],'01/01/1970'),(0)))

in the DDL, I would like to run a command on the database to retrieve that "AS" statement so I can compare it to its expected value. (I'm developing a SQL parser that will compare an existing database to a DDL definition)...

Is this possible?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Rimer
  • 2,054
  • 6
  • 28
  • 43

2 Answers2

27

This works in SQL Server 2008

create table dbo.Foo
(
StartDate int,
CallDT AS (CONVERT([datetime],dateadd(second,[StartDate],'01/01/1970'),(0)))
)

select definition 
from sys.computed_columns 
where name='CallDT' and object_id=object_id('dbo.Foo')
Martin Smith
  • 438,706
  • 87
  • 741
  • 845
12

Try this:

SELECT
    name, definition    
FROM 
    sys.computed_columns

Should work in SQL Server 2005 and newer.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459