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,
    Giammarco

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

    Marc

    If you want to use the INFORMATION_SCHEMA views, then try

    SELECT 
    COLUMNPROPERTY(OBJECT_ID(TABLE_SCHEMA+'.'+TABLE_NAME),COLUMN_NAME,'IsComputed') 
        AS IS_COMPUTED,
    *
    FROM INFORMATION_SCHEMA.COLUMNS
    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 
        sysobjects.name AS TableName, 
        syscolumns.name AS ColumnName
    FROM syscolumns
        INNER JOIN sysobjects
        ON syscolumns.id = sysobjects.id
        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.