In SQL Server, there are a couple of system catalog views that enable you to get a list of computed columns in a database.

One of these views is called sys.computed_columns. The other is sys.columns.

The sys.computed_columns View

The sys.computed_columns system catalog view contains one row for each computed column in the database. Therefore, you can run a simple query to get a list of computed columns.

SELECT 
  OBJECT_NAME(object_id) AS [Parent],
  name AS [Computed Column],
  definition,
  is_persisted
FROM sys.computed_columns;

Result:

+----------+-------------------+--------------------------------------+----------------+
| Parent   | Computed Column   | definition                           | is_persisted   |
|----------+-------------------+--------------------------------------+----------------|
| Person   | FullName          | (concat([FirstName],' ',[LastName])) | 0              |
| Products | TotalValue        | ([Quantity]*[Price])                 | 1              |
+----------+-------------------+--------------------------------------+----------------+

Most of this view’s columns are inherited from the sys.columns view. I’ve only included a handful here.

One of the benefits of this view over sys.columns, is that it returns the computed column’s definition, which may be useful depending on the situation. It also includes the is_persisted flag, which tells you whether or not the computed column is persisted. If the column is persisted, the column’s computed value is physically stored in the table. If not, it’s computed at the time you query the column.

The sys.columns View

You can also run a query against sys.columns to return computed columns. If you do this, you’ll need to filter the results to only include computed columns. You can do this with a WHERE clause on the is_computed column.

Example:

SELECT 
  OBJECT_NAME(object_id) as Parent,
  name AS [Computed Column]
FROM sys.columns
WHERE is_computed = 1;

Result:

+----------+-------------------+
| Parent   | Computed Column   |
|----------+-------------------|
| Person   | FullName          |
| Products | TotalValue        |
+----------+-------------------+

This view doesn’t contain the definition, is_persisted, or the uses_database_collation columns that sys.computed_columns contains.