获取数据库表中计算列的列表 (SQL Server)
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1484147/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Get List of Computed Columns in Database Table (SQL Server)
提问by Gia
Would any of you know how to get the list of computed columns in a SQL Server database table?
你们中有人知道如何获取 SQL Server 数据库表中的计算列列表吗？
I found sys.sp_help tablename does return this information, but only in the second result-set.
我发现 sys.sp_help tablename 确实返回此信息，但仅在第二个结果集中。
I am trying to find out if there is a better way of doing this. Something which only returns a single result set.
我试图找出是否有更好的方法来做到这一点。只返回单个结果集的东西。
回答by marc_s
Check the sys.columnssystem catalog view:
检查sys.columns系统目录视图：
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.
这适用于 SQL Server 2005 及更高版本。
UPDATE: There's even a sys.computed_columnssystem catalog view which also contains the definition (expression) of the computed column - just in case that might be needed some time.
更新：甚至还有一个sys.computed_columns系统目录视图，其中还包含计算列的定义（表达式） - 以防万一可能需要一段时间。
SELECT *
FROM sys.computed_columns
WHERE object_id = OBJECT_ID('YourTableName')
回答by Dave Poole
If you want to use the INFORMATION_SCHEMAviews, then try
如果要使用INFORMATION_SCHEMA视图，请尝试
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>'
回答by Ian Boyd
For SQL Server 2000 the syntax is:
对于 SQL Server 2000，语法是：
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)
回答by J Sullivan
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
