获取数据库表中计算列的列表 (SQL Server) [英] Get List of Computed Columns in Database Table (SQL Server)

问题描述

有谁知道如何获取 SQL Server 数据库表中计算列的列表?

Would any of you know how to get the list of computed columns in a SQL Server database table?

我发现 sys.sp_help tablename 确实返回了此信息，但仅在第二个结果集中.

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.

推荐答案

检查 sys.columns 系统目录视图:

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

这适用于 SQL Server 2005 及更高版本.

This works on SQL Server 2005 and up.

更新:甚至还有一个 sys.computed_columns 系统目录视图，其中还包含计算列的定义(表达式) - 以防万一可能需要一段时间.

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

这篇关于获取数据库表中计算列的列表 (SQL Server)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！