Get List of Computed Columns in Database Table (SQ

问题:

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.

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

回答1:

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

回答2:

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

回答3:

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)