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

63lcw9qa  于 11个月前  发布在  SQL Server
关注(0)|答案(5)|浏览(130)

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.

bnl4lu3b

bnl4lu3b1#

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

iyfamqjs2#

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

dnph8jn43#

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

tvmytwxo4#

If you have many tables with computed columns and want to see the table names as well:

SELECT sys.objects.name, sys.computed_columns.name
from sys.computed_columns 
inner join sys.objects on sys.objects.object_id = sys.computed_columns.object_id
order by sys.objects.name
ogsagwnx

ogsagwnx5#

Building upon Ian Boyd's answer you can also get the computed text (SQL query) and a sample select statement to test the calculated field using this query:

SELECT 
    sysobjects.name AS TableName, 
    syscolumns.name AS ColumnName,
    'SELECT ' + syscolumns.name + ' FROM ' + sysobjects.name as TestQuery,
    (SELECT TOP 1 definition FROM sys.computed_columns WHERE name = syscolumns.name) as Computed_Text
FROM syscolumns
INNER JOIN sysobjects ON syscolumns.id = sysobjects.id AND sysobjects.xtype = 'U' --User Tables
WHERE syscolumns.iscomputed = 1

Example:

相关问题