我正在尝试使用INFORMATION_SCHEMA访问列描述属性
我在过去创建了这个查询来获取列名,但我不知道如何获取列的描述
SELECT COLUMN_NAME AS Output, ORDINAL_POSITION
FROM INFORMATION_SCHEMA.COLUMNS
WHERE (TABLE_NAME = @Tablename) AND (ORDINAL_POSITION = @Location)
这是字段属性中描述的位置
发布于 2013-03-02 00:38:18
如果您所说的“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
发布于 2017-06-07 18:19:23
如果您特别想使用INFORMATION_SCHEMA (和我一样),那么下面的查询应该可以帮助您获得列的description字段:
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
发布于 2013-03-02 00:16:12
exec sp_columns @表名...这是为您提供信息的系统存储过程。
除此之外,这里还有一篇关于信息模式视图的好信息的文章:What is the equivalent of 'describe table' in SQL Server?
https://stackoverflow.com/questions/15161505
复制相似问题