SQL Server: Computed Column defintions retrievable

问题:

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?

回答1:

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')

回答2:

Try this:

SELECT
    name, definition    
FROM 
    sys.computed_columns

Should work in SQL Server 2005 and newer.