How to check if computed column is persisted?

Question

How to check if computed column is persisted? (MS SQL Server)

Somewhat related, not directly. Good read : http://stackoverflow.com/questions/916068/sql-server-2005-computed-column-is-persisted — Saurabh Gokhale, Mar 11 '11 at 12:36

score 11 · Accepted Answer · answered Mar 11 '11 at 12:31

Computed column attributes are available in sys.computed_columns.

select * from sys.computed_columns where is_persisted = 1

is_persisted = 1 for the column being persisted, 0 otherwise.

You can link this back to sys.tables via the object_id e.g.

select t.name, c.name
from sys.tables t
inner join sys.computed_columns c on c.object_id = t.object_id
where c.is_persisted = 1

And change your where clause to include the table name / field name as appropriate to your scenario.

score 0 · Answer 2 · answered Sep 26 '18 at 15:10

Necromancing.
If you need all columns, or just the computed columns, or just the persisted computed columns, you can do it with one script:

SELECT * 
FROM sys.columns AS sysc

LEFT JOIN sys.computed_columns AS syscc 
    ON syscc.object_id = sysc.object_id 
    AND syscc.name = sysc.name 

WHERE (1=1) 
AND sysc.is_computed = 1 -- optional 
AND is_persisted = 1  -- optional

How to check if computed column is persisted?

2 Answers2