Archive

Posts Tagged ‘sys.computed_columns’

Using Catalog Views – sys.computed_columns

20.06.2011 No comments

sys.computed_columns returns all columns that are computed in the database, below are some of the columns returned by sys.computed_columns:

1. Object_id – object id of the table to which the column belongs,

2. Name – name of the computed column,

3. Column_id – id of the column in the respective table,

4. Definition – computation used to generate the value for column,

5. Is_persisted – 1, if the column is persisted,

6. Is_computed – 1

 

— © 2011 – Vishal (http://SqlAndMe.com)

 

SELECT object_id, name, column_id, definition, is_persisted, is_computed

FROM   sys.computed_columns

Result Set:

image

 

To retrieve the Table Name to which the column belongs, you can join sys.computed_columns with sys.tables or use OBJECT_NAME() function as below:

SELECT OBJECT_NAME(object_id) AS 'Table Name', name, column_id,

       definition, is_persisted, is_computed

FROM   sys.computed_columns

Result Set:

image

Hope This Helps!

Vishal