Menu
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?
620
Some Limitations. You can not reference columns from other tables for a computed column expression directly. You can not apply insert or update statements on computed columns.
Get a list of computed columns in a SQL Server database. We can use the system function sys. computed_columns and join it with the sys. objects to get a list of available computed columns, their data type, and the column definition (formula).
A persisted computed column is one that is physically stored in the table. If you don't specify that it's persisted, then the column's value will be calculated each time you run a query against it. You can query the sys. computed_columns system catalog view to find out whether a computed column is marked as persisted.
Persisted computed columns It means that SQL Server physically stores the data of the computed columns on disk. When you change data in the table, SQL Server computes the result based on the expression of the computed columns and stores the results in these persisted columns physically.
Try this:
SELECT
name, definition
FROM
sys.computed_columns
Should work in SQL Server 2005 and newer.
54
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
