CODEWITHSUNDEEP
sqlsql-serverdatabasecalculated-columns
Rimer
Rimer

Reputation: 2074

SQL Server: Computed Column defintions retrievable from Database?

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?

Upvotes: 14

Views: 7721

Answers (2)

Martin Smith
Martin Smith

Reputation: 453328

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

Upvotes: 27

marc_s
marc_s

Reputation: 754598

Try this:

SELECT
    name, definition    
FROM 
    sys.computed_columns

Should work in SQL Server 2005 and newer.

Upvotes: 12

Related Questions