使用查询访问SQL中的列描述

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

这是描述在字段属性上的地方。

5 个解决方案

#1

If by 'description' you mean 'Description' displayed in SQL Management Studio in design mode, here it is:

如果“description”指的是设计模式下SQL Management Studio中显示的“description”，则为:

    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

#2

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?

除此之外，这里还有一篇关于信息模式视图的文章:在SQL Server中，什么相当于“describe table”?

#3

The fn_listextendedproperty system function will do what you're looking for (referred to as sys.fn_listextendedproperty for SQL Server 2012 on MSDN).

fn_listextendedproperty系统函数将执行所需的操作(称为sys)。fn_listextendedproperty用于MSDN上的SQL Server 2012)。

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 ScrapReason table in the Production schema

示例用法:列出生产模式中剪贴原因表的所有列的扩展属性。

USE AdventureWorks2012;
GO
SELECT objtype, objname, name, value
FROM fn_listextendedproperty (NULL, 'schema', 'Production', 'table', 'ScrapReason', 'column', NULL);
GO

sp_helptext will 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函数、触发器、计算列、检查约束、视图或系统对象(如系统存储过程)。

sp_columns does not return the sys.extended_properties.value field which you're looking for.

sp_columns不返回sys.extended_properties。你要找的值字段。

fn_listextendedproperty is arguably easier to work with and more generic than the query in the accepted answer.

可以认为，fn_listextendedproperty更容易使用，而且比已接受的答案中的查询更通用。

#4

Are you looking for the information in the sys.extended_properties view?

你在系统里找资料吗?extended_properties视图?

https://*.com/a/15008885/1948904

https://*.com/a/15008885/1948904

#5

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