SQL Server字段注释教程：扩展属性、sp_addextendedproperty与数据字典批量生成

1. 刚接手老库那天我才知道SQL Server的注释不是写在建表语句里的如果你被叫去整理一个跑了七八年的SQL Server数据库打开设计表页面发现几十张表、几百个字段全靠开发时的记忆硬撑字段名全是缩写——UserNm、CreDt、UpdBy——你大概率会和我当初一样先骂一句“为什么没有注释”然后才开始认真研究怎么给SQL Server数据库表字段添加注释SQL。表字段注释这件事SQL Server和MySQL完全是两套逻辑。MySQL的字段注释直接写在列定义里导出建表脚本就能看到SQL Server却把它存在另一套叫“扩展属性Extended Properties”的元数据机制里靠sp_addextendedproperty、sp_updateextendedproperty、sp_dropextendedproperty三个存储过程分别完成添加、修改、删除。这篇就把这套机制完整讲透从底层原理到直接可复制的SQL再带上执行演示和踩坑总结适合正在补数据字典、做系统交接、或者被领导要求“把所有字段说明补上”的同学。1.1 字段注释的本质扩展属性一个独立的元数据层很多从MySQL转过来的人第一反应是在ALTER TABLE里写COMMENT 说明结果发现SQL Server压根不认。原因很简单SQL Server从2000版本开始就设计了扩展属性机制数据库里几乎所有对象——数据库本身、架构、表、列、索引、约束、存储过程参数——都可以挂上自定义属性。字段注释本质上就是给某个字段挂了一条名为MS_Description的扩展属性值是你的注释文字。打个比方MySQL是把说明印在快递盒上而SQL Server是给快递盒额外贴了一张标签。快递盒表结构本身没变标签扩展属性贴在盒子外面。所以你会看到一个奇怪现象用SELECT * FROM 表查不到注释用sp_help看字段信息也不显示但SSMS设计表界面的“说明”栏里却有文字。如果不了解这套机制你会以为注释根本不存在。扩展属性底层存在sys.extended_properties系统视图里添加一条属性本质是往这个视图对应的系统表插一条记录。属性值类型是sql_variant可以存字符串、数字、日期但在字段注释场景下基本都存字符串。属性名理论上可以随便起只是SSMS和数据字典工具都约定俗成用MS_Description中文版SSMS里的“说明”栏就是它。1.2 四级定位数据库、架构、表、字段要操作扩展属性必须先能准确“定位”到目标对象。SQL Server扩展属性支持四个层级从粗到细是数据库级别level0、架构级别level1、对象级别level2再往下还有对象内的成员级别level3。但在表字段注释场景里我们实际用到的是三个层级SCHEMA架构、TABLE表、COLUMN列。所以每次执行添加注释的SQL都要重复写一遍从架构到表的完整路径。举个例子给dbo.Users表的Id字段加注释在存储过程里需要写level0type NSCHEMA, level0name Ndbo、level1type NTABLE, level1name NUsers、level2type NCOLUMN, level2name NId。一个都不能省因为只有把这几个定位参数组合起来才能精确锁定“哪个架构下、哪张表、哪个字段”。这套四级定位也是后来批量生成注释脚本的基础。理解了它你看到一大串参数就不会晕无非是从粗到细告诉数据库你要找谁。1.3 和MySQL、Oracle的直观对比为了让你对这套机制印象更深我列个简单对比。MySQL的字段注释在information_schema.columns的COLUMN_COMMENT字段里写在列定义中迁移时跟着建表语句走Oracle的字段注释用COMMENT ON COLUMN 表.字段 IS 说明是独立于表结构之外的对象SQL Server则用扩展属性体系。三者思路完全不同没有谁对谁错但SQL Server的扩展属性显然更灵活——你能自定义属性名。比如除了MS_Description我还会用Owner属性标记字段负责人、用SourceSystem标记数据来源这在后面做数据治理时非常实用。只是大部分人只用了它最基础的能力存注释。2. 给字段加注释sp_addextendedproperty用法拆解先从最常用的添加注释说起。存储过程名字比较长但参数规律性很强学会一个就能举一反三。2.1 完整语法逐参数拆解EXEC sys.sp_addextendedproperty name NMS_Description, value N用户ID主键自增, level0type NSCHEMA, level0name Ndbo, level1type NTABLE, level1name NUsers, level2type NCOLUMN, level2name NId;各参数含义如下参数作用必填name扩展属性名字段注释场景固定用MS_Description是value属性值也就是你写的注释内容是level0type第一级类型通常为SCHEMA否默认数据库级时可不写level0name架构名通常是dbo否level1type第二级类型通常为TABLE否level1name表名否level2type第三级类型字段注释场景为COLUMN否level2name字段名否注意level0type和level1type不是每次都必须写。如果只给数据库本身加注释后面全不写给表加注释时写到level1type为止给字段加注释时到底三级全齐。参数顺序必须从粗到细且每级type和name成对出现不能跨级跳。2.2 一次给一张表的所有字段补上注释实际工作中没人一个字段一个字段手敲通常是拿到表结构后批量写。我拿一个精简版用户表举例你直接复制改表名字段名就能用-- 假设已经有这张表 CREATE TABLE dbo.Users ( Id INT IDENTITY(1,1) NOT NULL, UserName NVARCHAR(50) NOT NULL, Email NVARCHAR(100) NULL, CreatedAt DATETIME2(3) NOT NULL ); GO -- 给Id字段加注释 EXEC sys.sp_addextendedproperty name NMS_Description, value N用户主键自增ID, level0type NSCHEMA, level0name Ndbo, level1type NTABLE, level1name NUsers, level2type NCOLUMN, level2name NId; -- 给UserName字段加注释 EXEC sys.sp_addextendedproperty name NMS_Description, value N用户名登录账号, level0type NSCHEMA, level0name Ndbo, level1type NTABLE, level1name NUsers, level2type NCOLUMN, level2name NUserName; -- 给Email字段加注释 EXEC sys.sp_addextendedproperty name NMS_Description, value N邮箱可为空, level0type NSCHEMA, level0name Ndbo, level1type NTABLE, level1name NUsers, level2type NCOLUMN, level2name NEmail; -- 给CreatedAt字段加注释 EXEC sys.sp_addextendedproperty name NMS_Description, value N创建时间, level0type NSCHEMA, level0name Ndbo, level1type NTABLE, level1name NUsers, level2type NCOLUMN, level2name NCreatedAt;写到这里你可能会问每个字段之间需不需要用GO隔开我的经验是在SSMS里一起选中执行没问题但如果某个语句报错错误定位会稍微费点眼。习惯上我会在每条EXEC之间加GO让脚本独立提交也方便排查问题。2.3 关于name和value几个容易翻车的细节第一value类型是sql_variant直接传字符串没问题但不要传NULL。传NULL确实能把属性建出来只是值为空等于注释了个寂寞。如果注释内容含数字比如value 100会被当成int存进去显示时可能没有类型问题但为了统一我建议一律用N...字符串形式避免类型混用。第二name虽然理论上可自定义但和SSMS界面联动的只有MS_Description。你起个Remark名字SSMS设计表里不会显示数据字典工具也读不到。如果不是做自定义元数据老老实实用MS_Description。第三存储过程名我习惯写sys.sp_addextendedproperty带上sys.前缀避免某些数据库里出现同名用户存储过程导致执行错对象。这个习惯救过我一次——有套老系统里真有开发建了个sp_addextendedproperty自定义过程不带前缀执行直接调错。第四也是最容易踩的同一字段同一属性名重复添加会直接报错“数据库中已存在名称为MS_Description的属性”。SQL Server的扩展属性没有upsert机制添加就是添加你要么先删再加要么用修改的存储过程。3. 修改与删除注释两个文档里很少强调的坑添加注释只是第一步项目运转半年后字段含义变了、注释写错了、或者干脆不要注释了这时候就需要修改和删除。3.1 修改注释sp_updateextendedproperty修改注释的存储过程叫sp_updateextendedproperty参数和添加几乎一模一样只是语义从“插入”变成了“更新”。EXEC sys.sp_updateextendedproperty name NMS_Description, value N用户唯一标识自增ID不允许为空, level0type NSCHEMA, level0name Ndbo, level1type NTABLE, level1name NUsers, level2type NCOLUMN, level2name NId;这里有个大坑如果目标字段当前并没有MS_Description属性执行sp_updateextendedproperty会报错“数据库中没有名为MS_Description的属性”。你先得确认这个字段之前有没有加过注释。尤其是当你从别的环境拿来一份脚本跑的时候源库有注释、目标库没有一执行就挂。所以我的习惯是在更新前先查一下有没有这条属性有则改没有则添加。后面第4章会给出完整的判断查询。3.2 删除注释sp_dropextendedproperty删除更简单注意参数里没有valueEXEC sys.sp_dropextendedproperty name NMS_Description, level0type NSCHEMA, level0name Ndbo, level1type NTABLE, level1name NUsers, level2type NCOLUMN, level2name NId;同样如果这个字段没有这条属性删除也会报错。错误消息大概是“数据库中不存在名称为MS_Description的属性”。我在自动化脚本里一般先判断再删除或者干脆在删除语句外面包一层IF EXISTS。顺带说一下删除字段注释不会影响表结构和数据扩展属性是独立元数据删了也无需担心数据安全。但要注意如果是字段本身被删了扩展属性会自动跟着清理如果是重建表原来的扩展属性就没了得重新添加。3.3 用一条SQL查出现在到底有没有注释这个查询我几乎每天都用直接复制即可SELECT s.name AS SchemaName, t.name AS TableName, c.name AS ColumnName, ep.value AS Comment FROM sys.tables t INNER JOIN sys.schemas s ON t.schema_id s.schema_id INNER JOIN sys.columns c ON t.object_id c.object_id LEFT JOIN sys.extended_properties ep ON ep.class 1 AND ep.major_id t.object_id AND ep.minor_id c.column_id AND ep.name NMS_Description WHERE s.name Ndbo AND t.name NUsers;返回结果里Comment为NULL的字段表示没有注释。有了这条SQL你可以在执行修改和删除前先看一眼也可以在补注释时快速识别哪些字段是漏网之鱼。class 1表示对象或列级别的扩展属性major_id是表对象IDminor_id是列ID——这段过滤逻辑也是第5章批量脚本的基础。除了手动查sys.extended_propertiesSQL Server还内置了fn_listextendedproperty函数可以按层级递归返回属性列表SELECT * FROM fn_listextendedproperty( NMS_Description, NSCHEMA, Ndbo, NTABLE, NUsers, NCOLUMN, NId );注意这里要传的层级参数是(name, level0type, level0name, level1type, level1name, level2type, level2name)顺序和存储过程一致。七个参数都能设NULL设NULL时表示返回该层级以下所有匹配项。我更习惯用sys.extended_properties因为写JOIN方便可定制性强。3.4 SSMS界面里的说明列背后就是这套存储过程如果你不想写SQLSSMS设计表界面也能操作。右键表名→设计选中列下方“列属性”面板里有一栏“说明”。填上内容保存后SQL Server会后台调用扩展属性相关存储过程写入MS_Description属性。用过几次你就会发现SSMS这个界面有个问题如果列没有属性你直接填写保存它会执行添加如果你清空“说明”保存它会删掉属性。但当你对几十个字段批量编辑时SSMS会一个字段一个字段地生成ALTER语句和扩展属性语句执行效率低还会产生大量无谓的锁和日志。我的习惯是还在开发阶段的表直接用SQL脚本维护注释交付后的维护阶段偶尔改一两个字段用界面凑合批量还是靠脚本。4. 一次跑通的完整演示建表→注释→修改→删除→再添加下面给一套完整可复现的演示脚本包含从建库到最终清理的全流程你按顺序执行就能看到每个环节的效果。4.1 完整脚本USE master; GO -- 如果存在演示库则删除重建 IF DB_ID(CommentDemo) IS NOT NULL BEGIN ALTER DATABASE CommentDemo SET SINGLE_USER WITH ROLLBACK IMMEDIATE; DROP DATABASE CommentDemo; END GO CREATE DATABASE CommentDemo; GO USE CommentDemo; GO -- 1. 建表 CREATE TABLE dbo.Products ( ProductId INT IDENTITY(1,1) NOT NULL, ProductName NVARCHAR(100) NOT NULL, Price DECIMAL(18,2) NULL, Stock INT NULL ); GO -- 2. 添加字段注释 EXEC sys.sp_addextendedproperty name NMS_Description, value N商品主键, level0type NSCHEMA, level0name Ndbo, level1type NTABLE, level1name NProducts, level2type NCOLUMN, level2name NProductId; EXEC sys.sp_addextendedproperty name NMS_Description, value N商品名称, level0type NSCHEMA, level0name Ndbo, level1type NTABLE, level1name NProducts, level2type NCOLUMN, level2name NProductName; EXEC sys.sp_addextendedproperty name NMS_Description, value N销售单价, level0type NSCHEMA, level0name Ndbo, level1type NTABLE, level1name NProducts, level2type NCOLUMN, level2name NPrice; EXEC sys.sp_addextendedproperty name NMS_Description, value N库存数量, level0type NSCHEMA, level0name Ndbo, level1type NTABLE, level1name NProducts, level2type NCOLUMN, level2name NStock; GO -- 3. 查看当前注释 SELECT t.name AS TableName, c.name AS ColumnName, ep.value AS Comment FROM sys.tables t INNER JOIN sys.columns c ON t.object_id c.object_id LEFT JOIN sys.extended_properties ep ON ep.class 1 AND ep.major_id t.object_id AND ep.minor_id c.column_id AND ep.name NMS_Description WHERE t.name NProducts ORDER BY c.column_id; GO -- 4. 修改注释 EXEC sys.sp_updateextendedproperty name NMS_Description, value N商品唯一标识, level0type NSCHEMA, level0name Ndbo, level1type NTABLE, level1name NProducts, level2type NCOLUMN, level2name NProductId; GO -- 5. 删除Stock字段的注释 EXEC sys.sp_dropextendedproperty name NMS_Description, level0type NSCHEMA, level0name Ndbo, level1type NTABLE, level1name NProducts, level2type NCOLUMN, level2name NStock; GO -- 6. 再次验证 SELECT t.name AS TableName, c.name AS ColumnName, ISNULL(ep.value, N无注释) AS Comment FROM sys.tables t INNER JOIN sys.columns c ON t.object_id c.object_id LEFT JOIN sys.extended_properties ep ON ep.class 1 AND ep.major_id t.object_id AND ep.minor_id c.column_id AND ep.name NMS_Description WHERE t.name NProducts ORDER BY c.column_id;4.2 验证结果长什么样第3步执行后你会看到四行记录分别对应四个字段的注释。第4步修改后ProductId的注释从“商品主键”变成“商品唯一标识”。第5步删除后第6步查询时Stock字段显示为“无注释”其他三个字段正常。如果你在sp_updateextendedproperty和sp_dropextendedproperty执行前没有先加过对应的注释会直接报错。第3步就是用来“探路”的自动化脚本里建议加判断说明见3.3节和5.2节。4.3 三张表带你看清添加、修改、删除的参数差异为了让你直观记住三个存储过程的异同我把它们放一起对比存储过程valuelevel类型属性不存在时行为sp_addextendedproperty必须全级别可选正常添加sp_updateextendedproperty必须全级别可选报错需先添加sp_dropextendedproperty不需要全级别可选报错需先添加value那一列是最大的差异点添加和修改都要传新值删除则完全不需要传值。很多新手写删除语句时习惯性带上value结果报错“过程 expects parameter valuewhich was not supplied”的反向——其实是没有value才对的带上了反而错。这里多说一句这三个存储过程的参数都支持命名参数和位置参数两种写法但我不建议用位置参数因为参数太多顺序一旦记错就会把value当成name用报错信息还很隐晦。5. 实战进阶整库字段注释的批量管理与数据字典生成单表操作会了接下来是真正能帮你省下几天的进阶玩法把整库的注释导成数据字典以及给“所有没注释的字段”批量生成添加脚本。这两种场景在项目交接和补文档时出现频率极高。5.1 一条SQL导出整库字段注释有了第3章的查询基础去掉WHERE条件里的表名再关联上表类型过滤就能得到整个库的字段注释清单SELECT s.name AS SchemaName, t.name AS TableName, c.name AS ColumnName, ty.name AS DataType, c.max_length AS MaxLength, c.is_nullable AS IsNullable, CAST(ep.value AS NVARCHAR(MAX)) AS Comment FROM sys.tables t INNER JOIN sys.schemas s ON t.schema_id s.schema_id INNER JOIN sys.columns c ON t.object_id c.object_id INNER JOIN sys.types ty ON c.user_type_id ty.user_type_id LEFT JOIN sys.extended_properties ep ON ep.class 1 AND ep.major_id t.object_id AND ep.minor_id c.column_id AND ep.name NMS_Description ORDER BY s.name, t.name, c.column_id;这个结果贴到Excel里就是一份最基础的数据字典包含表名、字段名、数据类型、长度、是否可空、注释。如果想让注释排到第一列调整SELECT顺序即可。再往深做还能把主键、外键、索引信息JOIN进来生成更完整的数据字典这是后话。注意CAST(ep.value AS NVARCHAR(MAX))这个写法——ep.value是sql_variant类型直接导出到Excel时有时显示为二进制或乱码CAST成字符串最稳。如果你用的是低版本SQL Server遇到sql_variant转nvarchar报转换错误可以用CONVERT(NVARCHAR(MAX), ep.value)代替。5.2 给所有没注释的字段批量生成添加脚本有些老库整库都没注释你不可能一个个手敲。用下面这条SQL自动生成每个字段的sp_addextendedproperty执行语句你在SSMS里把结果复制出来跑一遍所有字段注释就全有了——前提是你得先定义好每个字段的注释来源。这里给两个思路如果原系统里有过一张字段说明Excel你可以把Excel导入一张临时表关联生成如果没有现成的说明先批量生成占位注释如N待补充之后再逐步修改。我更推荐后者至少能保证每个字段都有属性占位后续用sp_updateextendedproperty逐个更新比从零添加省事得多。占位注释生成脚本示例如下其中中文占位内容实际使用时请替换为你的说明文字SELECT NEXEC sys.sp_addextendedproperty Nname NMS_Description, Nvalue N待补充, Nlevel0type NSCHEMA, level0name N s.name N, Nlevel1type NTABLE, level1name N t.name N, Nlevel2type NCOLUMN, level2name N c.name N; FROM sys.tables t INNER JOIN sys.schemas s ON t.schema_id s.schema_id INNER JOIN sys.columns c ON t.object_id c.object_id LEFT JOIN sys.extended_properties ep ON ep.class 1 AND ep.major_id t.object_id AND ep.minor_id c.column_id AND ep.name NMS_Description WHERE ep.value IS NULL ORDER BY s.name, t.name, c.column_id;这个脚本里有个细节字符串拼接时外层用N包裹单引号因为SQL字符串里的单引号需要用两个单引号转义所以你会看到NMS_Description这种写法。如果我注释内容里本身带了单引号比如N用户ID没问题但如果是NIts a test就需要额外处理这我在第6章专门讲。5.3 数据库、表、索引也能加注释思路完全一致扩展属性不止能用在字段上。给数据库加注释去掉level1type及以后参数EXEC sys.sp_addextendedproperty name NMS_Description, value N核心业务库禁止直接修改表结构;给表加注释写到level1typeEXEC sys.sp_addextendedproperty name NMS_Description, value N商品基础信息表, level0type NSCHEMA, level0name Ndbo, level1type NTABLE, level1name NProducts;给索引加注释level2type为INDEX给约束加注释level2type为CONSTRAINT。原理都一样只是定位层级对应的对象类型不同。你在数据治理时如果想给表打上“核心表”“接口表”的标记完全可以用不同的属性名存储比如name NTableCategory。这比新建一张维护表轻量得多。6. 我踩过的那些关于注释的坑一次说清楚功能讲完把我在真实环境里踩过的坑集中说一说。这些坑不在官方文档里但几乎每个做数据字典的人都会遇到。6.1 注释内容里的单引号必须加倍处理假设你要写这么一个注释Its a test。直接拼进SQL里会报语法错误因为字符串里的单引号把整个字符串截断了。正确写法是把单引号翻倍EXEC sys.sp_addextendedproperty name NMS_Description, value NIts a test, level0type NSCHEMA, level0name Ndbo, level1type NTABLE, level1name NProducts, level2type NCOLUMN, level2name NProductName;字符串里的会被SQL解释成一个单引号。在5.2节的批量生成脚本里我做法是利用REPLACE函数先把原注释里的单引号替换成两个单引号再拼进动态SQL否则生成出来的语句根本没法执行。这个细节在手工维护注释时很容易忽略尤其是英文注释或者带中英文混合标点的注释。还有个编码相关的坑SQL脚本文件如果不是用UTF-8带BOM格式保存SSMS打开时中文可能直接显示成乱码执行进数据库后注释也是乱的。我现在的习惯是所有含中文注释的脚本一律用UTF-8 with BOM或GBK编码保存且字符串前统一加N前缀。N表示Unicode字符串能避免中文在不同数据库排序规则下出现乱码。6.2 重建表、导入导出、生成脚本时注释为什么总丢这是我在一次数据库重构中付出过代价的教训。当时我用SSMS的“生成脚本”功能把整库结构导出来在另一台服务器上执行建表跑完发现所有字段注释全部丢失。原因有两个一是生成脚本向导的“高级选项”里有一个“编写扩展属性脚本”开关默认在一些版本里不是全选状态需要手动勾上“True”二是如果脚本是从旧库生成后拿到新库执行库名、架构名对不上扩展属性定位失败也会静默丢失。更隐蔽的是SSMS的导入导出向导Import Data / Export Data它默认只搬运数据不搬运扩展属性。你从一个库导数据到另一个库字段注释不会跟着走。很多人以为表结构一样注释就会在实际操作完一看注释全没了。所以做库迁移时我的标准流程是先对比两边表结构再检查扩展属性最后迁移数据。结构层对比用sys.columns和sys.extended_properties两个视图联合检查注释差异用5.1节的查询导出来做Diff。数据迁移完注释该补的补、该改的改别指望工具自动帮你搬。6.3 修改列名或删列后扩展属性会怎样这个坑比较绕如果你用sp_rename修改列名扩展属性还在但因为sys.extended_properties里的minor_id关联的是列的内部ID而不是列名所以新列名仍能看到旧注释。听起来是好事但反过来如果注释文本里提到了旧列名就会产生语义偏差。比如原来有个字段Remark注释是“备注信息”你用sp_rename Remark, Comments改了列名注释不会自动变成“Comments信息”。这种“张冠李戴”的情况在维护老库时很常见数据库不会报错但数据字典看起来就很尴尬。我遇到过不止一次字段改名后注释里还是旧名词后来做数据治理时不得不全面排查一遍。如果你删除了某个字段它的扩展属性会跟着自动清理这个不用担心。但如果你是“先删列再加同名列”新列是一个全新的内部ID旧的注释不会回来需要重新添加。所以“删列重建”这种操作一旦涉及有注释的字段务必先把注释脚本留存一份。6.4 扩展属性还可以做更多事最后说点扩展性的思考。既然扩展属性是一套通用元数据机制完全可以不局限于MS_Description。我现在维护的一个系统里字段上同时挂着三类属性MS_Description放业务含义DataOwner放负责岗位SecurityLevel放敏感级别。查询所有敏感字段时一条SQL就能把标记了SecurityLevelL3的字段全捞出来配合脱敏工具做自动化处理。这个能力在做数据标准化、数据血缘、合规审查时都非常好用。你不需要额外建表不需要写复杂的维护界面只要在现有表结构上不断添加不同name的扩展属性就行。当然前提是团队约定好属性名规范否则每个人各起各的名字第二天就乱套。我在项目里会专门维护一份“扩展属性命名规范”文档比口头约定靠谱得多。根据我个人这几年的经验SQL Server字段注释这套机制真正用顺手之后是越用越离不开的。它不像MySQL那样“天然自带注释”需要你多了解一层扩展属性但换来的是远高于M.###此处删除重复内容的灵活度。下次再被问“这个字段什么意思”你可以理直气壮地说“查一下MS_Description注释里写得清清楚楚。”