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 secord resultset.

  • Multiple INSERT statements vs. single INSERT with multiple VALUES
  • How to format datetime as M/D/YYYY in SQL Server?
  • SQL statement with an impossible constant comparison
  • ADO parameterised query not returning any result
  • Managing SQL Connections in WCF Service
  • restore sql server from .bak file Exclusive access could not be obtained
  • I am trying to find out if there is a better way of doing this. Something which only returns a single result set.

    Any help is very appreciated, as this is very badly documented.

    Thank you,

    3 Solutions collect form web for “Get List of Computed Columns in Database Table (SQL Server)”

    Sure – 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')


    If you want to use the INFORMATION_SCHEMA views, then try

    WHERE TABLE_NAME='<Insert Your Table Name Here>'

    For SQL Server 2000 the syntax is:

    SELECT * FROM sys.columns
    WHERE is_computed = 1

    And the slightly more useful:

    SELECT AS TableName, AS ColumnName
    FROM syscolumns
        INNER JOIN sysobjects
        ON =
        AND sysobjects.xtype = 'U' --User Tables
    WHERE syscolumns.iscomputed = 1

    sample output:

    TableName              ColumnName
    =====================  ==========
    BrinksShipmentDetails  Total
    AdjustmentDetails      Total
    SoftCountDropDetails   Total
    CloserDetails          Total
    OpenerDetails          Total
    TransferDetails        Total
    (6 row(s) affected)
    MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.