给SQL server数据库表字段添加注释SQL，附修改、删除注释SQL及演示

目录

一. 前提小知识(数据库连接，数据库，SCHEMA，Table的关系)

二. 添加备注

2.1 添加备注基本语法(sys.sp_addextendedproperty)

2.2 SQL演示

2.3 fn_listextendedproperty函数查询备注个数

2.4 开发常用添加注释语法

三. 修改备注

3.1 修改备注基本语法（sys.sp_updateextendedproperty）

3.2 需要注意的坑

3.3 添加注释与修改注释的搭配使用

四. 删除备注

4.1 删除备注基本语法

---

一. 前提小知识(数据库连接，数据库，SCHEMA，Table的关系)

数据库我们比较熟悉的就是数据库DataBase，然后就是数据库中的表Table；

但在它们二者中间，还有一个中间者，叫"SCHEMA"，一个数据库可以有多个"SCHEMA"，"SCHEMA"是数据库的一个逻辑结构，，用于组织数据库对象，如表、视图、存储过程等。如下图所示，

（1）数据库连接(数据库服务器)：我们知道，数据库都是一个个的链接，我们都过IP和端口去连接各个数据库服务器，所以连接是最大的一个单位；

（2）数据库：其次，我们就会发现，在连接的数据库服务器中，会有1~多个数据库DataBase，通常情况下一个项目会有多个数据库；

（3）SCHEMA：再次，在每个数据库DataBase下，还会有多个SCHEMA，每个SCHEMA都是一个独立的小单位，它们之间互相隔绝，每个SCHEMA都有自己的权限和对象，从而实现更好的数据隔离和安全性。

（4）Table表：最后，在每个SCHEMA之下没我们可以建立多张表，这个大家都很熟悉，就不多说了，每张表存储着不同的实体对象数据；

总结下来，一个数据库连接(数据库服务器)下可以有多个数据库(DataBase)，一个数据库下可以有多个SCHEMA，一个SCHEMA下可以建立多张数据表。所以它们的关系就是 数据库连接>数据库>SCHEMA>Table；了解了这个小知识，我们开始进入正题。

二. 添加备注

2.1 添加备注基本语法(sys.sp_addextendedproperty)

在SQL server中，使用"sys.sp_addextendedproperty"即可向表中的字段添加注释。

举例：现有一个表名叫 "MyTable"，表中有一个列的字段叫 "MyColumn"。

如下SQL，就是给字段 MyColumn 添加注释，注释即为 "MyColumn字段的描述"。

EXEC sys.sp_addextendedproperty
-- @name=N'MS_Description'添加备注的描述，固定写法@value=N'这是MyColumn字段的描述', 
     @name=N'MS_Description',@value=''就是我们要给字段添加的备注内容                //
-- @level0type=N'SCHEMA' 指定了级别0的类型为架构，name则指架构的名称为dbo。
     @level0type=N'SCHEMA', @level0name=N'dbo',
-- @level0name=N'dbo' 指定了级别1的类型为表,name则指表名为MyTable。    
     @level1type=N'TABLE', @level1name=N'MyTable',
-- @level2type=N'COLUMN' 指定了级别2的类型为列，name指要添加注释的列名为MyColumn
     @level2type=N'COLUMN', @level2name=N'MyColumn'; 

 

2.2 SQL演示

(a)如下图所示，在本地连接 'local' 中，有数据库 'fccbdb' ，数据库下有 SCHEAM 叫 'dbo'，现在我使用上述SQL给主键ID添加注释；

(b)SQL如下 

-- 给 log_fccb 表中的 ID 字段添加注释
EXEC sys.sp_addextendedproperty
    @name=N'MS_Description',                      
    @value=N'主键ID',
    @level0type=N'SCHEMA', @level0name=N'dbo',    
    @level1type=N'TABLE', @level1name=N'log_fccb',
    @level2type=N'COLUMN', @level2name=N'ID'; 

(c)然后运行上述 SQL 语句，我们再打开 log_fccb 表，就会看到此时的主键ID已经被添加上注释了；

(d)此外，fccbdb下 还有 SCHEMA 叫 guest，但并没有任何的表数据，我们一会做对比看一下，将上述SQL语句中的SCHEMA由dbo改为guest再次执行；

EXEC sys.sp_addextendedproperty
    @name=N'MS_Description',                      
    @value=N'新注释主键ID',
    @level0type=N'SCHEMA', @level0name=N'guest',    
    @level1type=N'TABLE', @level1name=N'log_fccb',
    @level2type=N'COLUMN', @level2name=N'ID'; 

 

(e)执行上述SQL语句，就会出现下方的报错信息，错误信息"guest.log_fccb.ID"不存在，这也是正常的，我们刚才说了。guest下是空的，自然没有表，也没有名叫ID的字段；所以也侧面印证了SCHEMA是表的上一级，需要在SQL语句中指定某个SCHEMA下的某张表的某个字段，才能去通过SQL为其添加注释。

2.3 fn_listextendedproperty函数查询备注个数

在SQL server中，提供了 fn_listextendedproperty 函数，通过它可以返回某个列的备注数量，通常为0或者1，为0表示该字段没有被添加备注，为1则表示当前字段已经有备注。

(a)使用语法如下，其中括号内的8个参数与上面 sp_addextendedproperty 添加注释函数中的8个参数一样，但是查询不需要写"@name=N'MS_Description', @value=N'错误信息'，所以只剩下6个参数"，还可以将前面的@xxx省略。

-- 查询log_fccb表中字段ID的备注个数
SELECT COUNT(*) 
FROM fn_listextendedproperty
('MS_Description', 'SCHEMA', 
'dbo', 'TABLE', 
'log_fccb', 'COLUMN', 'ID')

(b)运行上述SQL，即可得到下图所示结果，显示 field 为1，表示当前ID字段有一个注释；

 

(c)我们可以在换一个字段，换成当前 log_fccb 表 errorMsg 字段，再次编辑执行SQL，如下图所示，得到的结果field就是0；

 (d)然后我们打开 log_fccb 数据表结构，如下图所示，errorMsg字段确实没有被添加注释；

 

2.4 开发常用添加注释语法

上面说的都是琐碎的语法，我们平常自己练习的时候可以使用，但在日常开发过程中，对数据库SQL脚本的要求是比较高的，最好不要出错，或者再执行脚本前去数据库中做判断。

（1）比如创建表，首先要判断是否存在，如果存在先删除，删除之后再创建；

（2）比如添加字段，要先判断字段是否存在，若存在先删除，删除之后再重新添加新的字段类型；

同理，注释也是一样的，我们再给表中的字段添加注释之前，要先判断当前字段是否已经存在注释，若存在则不添加；

此时，我们就可以将刚才的两个函数结合使用，再搭配 IF 语句做判断；

(a)SQL如下所示

-- IF做判断，括号内即为 fn_listextendedproperty 函数，返回值为0或1，
-- 如果为0，说明做判断的字段没有备注，就调用 sp_addextendedproperty 
-- 函数为其添加备注，如果返回值为1不等于0，则后面的函数也不会执行，
-- 由此就达到了没有备注添加备注有备注则不做任何操作的目的
IF (
	SELECT COUNT(*) 
	FROM fn_listextendedproperty('MS_Description', 'SCHEMA', 'dbo', 'TABLE', 'log_fccb', 'COLUMN', 'errorMsg')
) = 0
EXEC sp_addextendedproperty 
@name=N'MS_Description', @value=N'错误信息',
@level0type=N'SCHEMA', @level0name=N'dbo',    
@level1type=N'TABLE', @level1name=N'log_fccb',
@level2type=N'COLUMN', @level2name=N'errorMsg'; 

(b)上面也说到了， fn_listextendedproperty 函数中的8个参数前置可以省略，同理 sp_addextendedproperty 也可以省略，省略完毕之后，我们的SQL就得到了极大的简化，简化后如下

IF (
		SELECT COUNT(*) 
	  FROM fn_listextendedproperty('MS_Description', 'SCHEMA', 'dbo', 'TABLE', 'log_fccb', 'COLUMN', 'errorMsg')
) = 0
EXEC sp_addextendedproperty
N'MS_Description', N'错误信息',
N'SCHEMA', N'dbo',
N'TABLE', N'log_fccb',
N'COLUMN', N'errorMsg';

(c)运行上述SQL，结果如下执行成功

 (d)然后我们再打开 log_fccb 表，就可以看到 errorMsg 字段已经被我们成功添加注释；

 

三. 修改备注

3.1 修改备注基本语法（sys.sp_updateextendedproperty）

在SQL server中，使用"sys.sp_updateextendedproperty"即可更新表中字段的注释。和刚才的添加注释方法几乎没有区别，只是换了一个函数；

(a)举例：继续拿上面的 log_fccb 表举例，将刚才给ID字段添加的注释 "主键ID" 修改为 "新的备注主键ID" ，SQL如下。

--  更新log_fccb 表中字段ID的备注为 "新的备注主键ID"
EXEC sys.sp_updateextendedproperty
N'MS_Description', N'新的注释主键ID', 
N'SCHEMA', N'dbo', 
N'TABLE', N'log_fccb', 
N'COLUMN', N'ID';

(b)执行SQL，如下所示显示成功，

 (c)然后我们打开 log_fccb 表，就可以看到主键ID的备注已经被修改为新的了；

3.2 需要注意的坑

(a)虽然使用 sys.sp_updateextendedproperty 可以帮助我们修改字段的备注，但是特也有坑需要我们注意，如果我们要修改备注的字段根本就没有添加备注，它就会修改失败；如下图所示，显示该字段没有 "MS_Description" 属性，该属性是在给字段添加过注释之后才有的，没有添加过注释的字段是没有的，所以会报错。

(b)如下图所示，我给 res_text 字段修改备注为"响应文本"，但实际上该字段原本没有备注，执行SQL，就会报出如下错误；

3.3 添加注释与修改注释的搭配使用

(c)上面我们将 fn_listextendedproperty 函数和 sp_addextendedproperty 函数搭配使用给没有备注的字段添加注释，这里我们可以继续扩展，再搭配上 sys.sp_updateextendedproperty 更新函数更新表的字段；

(d)SQL如下所示

-- 1. 判断 res_text 字段当前有没有注释，如果没有则为其添加注释 "响应文本666"
IF (
		SELECT COUNT(*) 
	  FROM fn_listextendedproperty('MS_Description', 'SCHEMA', 'dbo', 'TABLE', 'log_fccb', 'COLUMN', 'res_text')
) = 0
EXEC sp_addextendedproperty
N'MS_Description', N'响应文本666',
N'SCHEMA', N'dbo',
N'TABLE', N'log_fccb',
N'COLUMN', N'res_text'
-- 2. 判断当前res_text 字段当前有没有注释，如果有则把注释改为 "响应文本999"
else IF (
		SELECT COUNT(*) 
	  FROM fn_listextendedproperty('MS_Description', 'SCHEMA', 'dbo', 'TABLE', 'log_fccb', 'COLUMN', 'res_text')
) = 1
EXEC sys.sp_updateextendedproperty
N'MS_Description', N'响应文本999',
N'SCHEMA', N'dbo',
N'TABLE', N'log_fccb',
N'COLUMN', N'res_text';

(e)运行SQL，得到OK，说明执行成功， 

(f)打开 log_fccb 表，可以看到 res_text 字段 注释已经被改为 "响应文本999"目的达成

四. 删除备注

4.1 删除备注基本语法

(a)删除其实都没什么好说的，因为开发过程中我们几乎都是去添加或者修改注释，不会去删除注释，但还是提一嘴，删除注释函数为 dorp ，然后下方8个参数不需要写注释，因为要删掉注释，当然不需要写注释内容，所以剩下7个参数，其他语法不变；示例SQL如下所示；

-- 删除 log_fccb 表中 errorMsg 字段的备注
EXEC sys.sp_dropextendedproperty 
    @name=N'MS_Description', 
    @level0type=N'SCHEMA', @level0name=N'dbo', 
    @level1type=N'TABLE', @level1name=N'log_fccb', 
    @level2type=N'COLUMN', @level2name=N'errorMsg';

(b)执行此SQL，如下图OK成功

(c)打开 log_fccb 表，可以看到 errorMsg 字段的注释已经被删除了；