This article presents three ways to use T-SQL to find out if a column is a computed column in SQL Server.
This is for when you know the column name, but you don’t know whether or not it’s a computed column.
The COLUMNPROPERTY() Function
The COLUMNPROPERTY() function returns information about a given column.
One of the properties accepted as an argument by this function is called IsComputed. You’ll get a 1 if the column is computed, and a 0 if it’s not.
SELECT
COLUMNPROPERTY(
OBJECT_ID('dbo.Products'),
'TotalValue',
'IsComputed')
AS [Computed Column?];
Result:
+--------------------+ | Computed Column? | |--------------------| | 1 | +--------------------+
In this case I checked to see if the TotalValue column is a computed column, and the result is 1, which means that it is a computed column.
The sys.computed_columns System Catalog View
The sys.computed_columns system catalog view contains one row for each computed column in the database. You can therefore query this view to see if your column is computed.
SELECT is_computed AS [Computed Column?] FROM sys.computed_columns WHERE name = 'TotalValue';
Result:
+--------------------+ | Computed Column? | |--------------------| | 1 | +--------------------+
You can also use this view if you only know the table name. If you don’t know the name of the column, but you’re simply trying to find out if the table contains a computed column, you could do something like this:
SELECT name AS [Computed Column]
FROM sys.computed_columns
WHERE object_id = OBJECT_ID('dbo.Products');
Result:
+-------------------+ | Computed Column | |-------------------| | TotalValue | +-------------------+
In this case I knew the table name was Products, so I used OBJECT_ID() to get its ID and match that with the object_id column (which is the ID of the object to which the column belongs).
In these examples I’m only returning one column. As with any view, you can return as many columns as you like. One of the columns from this view contains the computed column’s definition. Here’s a query that returns all columns.
SELECT * FROM sys.computed_columns WHERE name = 'TotalValue';
Result (using vertical output):
object_id | 814625945 name | TotalValue column_id | 5 system_type_id | 60 user_type_id | 60 max_length | 8 precision | 19 scale | 4 collation_name | NULL is_nullable | 1 is_ansi_padded | 0 is_rowguidcol | 0 is_identity | 0 is_filestream | 0 is_replicated | 0 is_non_sql_subscribed | 0 is_merge_published | 0 is_dts_replicated | 0 is_xml_document | 0 xml_collection_id | 0 default_object_id | 0 rule_object_id | 0 definition | ([Quantity]*[Price]) uses_database_collation | 1 is_persisted | 1 is_computed | 1 is_sparse | 0 is_column_set | 0 generated_always_type | 0 generated_always_type_desc | NOT_APPLICABLE encryption_type | NULL encryption_type_desc | NULL encryption_algorithm_name | NULL column_encryption_key_id | NULL column_encryption_key_database_name | NULL is_hidden | 0 is_masked | 0 graph_type | NULL graph_type_desc | NULL
The sys.columns System Catalog View
The sys.computed_columns view actually inherits its is_computed column (and a bunch of other columns) from sys.columns. Therefore, you can also use sys.columns to check if a column is a computed column.
SELECT is_computed FROM sys.columns WHERE name = 'TotalValue';
Result:
+---------------+ | is_computed | |---------------| | 1 | +---------------+