sp_addextendedproperty 添加字段描述

最新推荐文章于 2021-09-27 09:08:36 发布

转载最新推荐文章于 2021-09-27 09:08:36 发布 · 477 阅读

数据库相关专栏收录该内容

27 篇文章

订阅专栏

本文介绍如何使用SQL Server的sp_addextendedproperty和sp_helpextendedproperty存储过程来查询和管理表、列的描述信息，包括如何添加、修改、删除以及查询描述文本。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

sp_addextendedproperty

DECLARE @v sql_variant
SET @v = N'这是相关描述'
EXECUTE sp_addextendedproperty N'MS_Description', @v, N'SCHEMA', N'dbo', N'TABLE', N't_newbaseball_a', N'COLUMN', N'f_rfczje'

查询描述

SELECT
    [Table Name] = OBJECT_NAME(c.object_id),
    [Column Name] = c.name,
    [Description] = ex.value
FROM
    sys.columns c
LEFT OUTER JOIN
    sys.extended_properties ex
ON
    ex.major_id = c.object_id
    AND ex.minor_id = c.column_id
    AND ex.name = 'MS_Description'
WHERE
    OBJECTPROPERTY(c.object_id, 'IsMsShipped')=0
    AND OBJECT_NAME(c.object_id) = 'mytablename'
ORDER
    BY OBJECT_NAME(c.object_id), c.column_id