How to find computed columns on a SQL Server table

25 May,2018 by Jack Vamvas

Question: I'm having a problem with an index rebuild job , which is failing on tables with computed columns. I've read  ALTER INDEX failed due to QUOTED IDENTIFIER set to OFF   which details a solution for situations of computed columns, xml data type and LOB data types.

How can I find the computed columns on sql tables?

Answer: These are two ways in which you can identify the computed columns. One method is through the sys.columns  and the other through sys.computed_columns . 

sys.computed_columns has the benefit of not requiring you to use the "is_computed = 1" predicate.

 

Method 1 - sys.columns

 

SELECT * FROM sys.columns
WHERE is_computed = 1
AND object_id = OBJECT_ID('My_Table')

Method 2 - sys.computed_columns

SELECT * FROM sys.computed_columns
WHERE object_id = OBJECT_ID('My_Table')


Author: Jack Vamvas (http://www.sqlserver-dba.com)


Share:

Verify your Comment

Previewing your Comment

This is only a preview. Your comment has not yet been posted.

Working...
Your comment could not be posted. Error type:
Your comment has been posted. Post another comment

The letters and numbers you entered did not match the image. Please try again.

As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.

Having trouble reading this image? View an alternate.

Working...

Post a comment on How to find computed columns on a SQL Server table


sqlserver-dba.com | SQL Server Performance Tuning | SQL Server DBA:Everything | FAQ | Contact|Copyright & Disclaimer