使用查询访问 SQL 中的列描述
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15161505/
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
Use a Query to access column description in SQL
提问by Will Peckham
I am trying to access the Column description properties using the INFORMATION_SCHEMA
我正在尝试使用 INFORMATION_SCHEMA 访问列描述属性
I have created this Query in the past to get the column name but i can not figure out how to get description of the column
我过去创建了这个查询来获取列名，但我不知道如何获取列的描述
SELECT COLUMN_NAME AS Output, ORDINAL_POSITION
FROM INFORMATION_SCHEMA.COLUMNS
WHERE (TABLE_NAME = @Tablename) AND (ORDINAL_POSITION = @Location)
This is where the Description is on the field properties
这是描述在字段属性上的位置
回答by Incidently
If by 'description' you mean 'Description' displayed in SQL Management Studio in design mode, here it is:
如果“描述”是指在设计模式下在 SQL Management Studio 中显示的“描述”，则为：
select
st.name [Table],
sc.name [Column],
sep.value [Description]
from sys.tables st
inner join sys.columns sc on st.object_id = sc.object_id
left join sys.extended_properties sep on st.object_id = sep.major_id
and sc.column_id = sep.minor_id
and sep.name = 'MS_Description'
where st.name = @TableName
and sc.name = @ColumnName
回答by Kez Floyd
If you specifically want to use INFORMATION_SCHEMA (as I was) then the following query should help you obtain the column's description field:
如果您特别想使用 INFORMATION_SCHEMA（就像我一样），那么以下查询应该可以帮助您获取列的描述字段：
SELECT COLUMN_NAME AS [Output]
,ORDINAL_POSITION
,prop.value AS [COLUMN_DESCRIPTION]
FROM INFORMATION_SCHEMA.TABLES AS tbl
INNER JOIN INFORMATION_SCHEMA.COLUMNS AS col ON col.TABLE_NAME = tbl.TABLE_NAME
INNER JOIN sys.columns AS sc ON sc.object_id = object_id(tbl.table_schema + '.' + tbl.table_name)
AND sc.NAME = col.COLUMN_NAME
LEFT JOIN sys.extended_properties prop ON prop.major_id = sc.object_id
AND prop.minor_id = sc.column_id
AND prop.NAME = 'MS_Description'
WHERE tbl.TABLE_NAME = @TableName
回答by Dennis T --Reinstate Monica--
The fn_listextendedpropertysystem function will do what you're looking for.
该fn_listextendedproperty系统功能会做你要找的东西。
It's also referred to as sys.fn_listextendedproperty.
它也被称为sys.fn_listextendedproperty。
Syntax is as follows:
语法如下：
fn_listextendedproperty (
{ default | 'property_name' | NULL }
, { default | 'level0_object_type' | NULL }
, { default | 'level0_object_name' | NULL }
, { default | 'level1_object_type' | NULL }
, { default | 'level1_object_name' | NULL }
, { default | 'level2_object_type' | NULL }
, { default | 'level2_object_name' | NULL }
)
Example Usage: Lists extended properties for all columns of the ScrapReasontable in the Productionschema
示例用法：列出架构ScrapReason中表的所有列的扩展属性Production
USE AdventureWorks2012;
GO
SELECT objtype, objname, name, value
FROM fn_listextendedproperty (NULL, 'schema', 'Production', 'table', 'ScrapReason', 'column', NULL);
GO
sp_helptextwill not work since it can't be used for tables as per TechNet.
sp_helptext将不起作用，因为它不能用于按照TechNet 的表格。
Displays the definition of a user-defined rule, default, unencrypted Transact-SQL stored procedure, user-defined Transact-SQL function, trigger, computed column, CHECK constraint, view, or system object such as a system stored procedure.
显示用户定义的规则、默认的、未加密的 Transact-SQL 存储过程、用户定义的 Transact-SQL 函数、触发器、计算列、CHECK 约束、视图或系统对象（如系统存储过程）的定义。
sp_columnsdoes not return the sys.extended_properties.valuefield which you're looking for.
sp_columns不会返回sys.extended_properties.value您要查找的字段。
fn_listextendedpropertyis arguably easier to work with and more generic than the query in the accepted answer.
fn_listextendedproperty可以说比接受的答案中的查询更容易使用并且更通用。
回答by Chains
exec sp_columns @Tablename... That's the system stored procedure that will give you the info.
exec sp_columns @Tablename... 这是系统存储过程，它将为您提供信息。
Other than that, here is a post with a lot of good information on the INFORMATION SCHEMA views: What is the equivalent of 'describe table' in SQL Server?
除此之外，这里有一篇关于 INFORMATION SCHEMA 视图的好信息的帖子：SQL Server 中“描述表”的等价物是什么？
回答by Drew Leffelman
Are you looking for the information in the sys.extended_properties view?
您是否在 sys.extended_properties 视图中查找信息？
