Search code examples
sqlsql-servert-sql

Get List of Computed Columns in Database Table (SQL Server)

Would any of you know how to get the list of computed columns in a SQL Server database table?

I found sys.sp_help tablename does return this information, but only in the second result-set.

I am trying to find out if there is a better way of doing this. Something which only returns a single result set.

Solution

  • Check the sys.columns system catalog view:

    SELECT *
FROM sys.columns
WHERE is_computed = 1

    This gives you all computed columns in this database.

    If you want those for just a single table, use this query:

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

    This works on SQL Server 2005 and up.

    UPDATE: There's even a sys.computed_columns system catalog view which also contains the definition (expression) of the computed column - just in case that might be needed some time.

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