如何利用SQL创建与修改列的说明信息(备注信息)详解

专注研发ERP低代码开发平台 2011-11-07 11:07:35

加精

相信很多朋友对利用SQL创建表已经很熟悉了，但我们发现在创建表的同时不能像添加默认值或者主键一样为列加上说明信息，所以我们经常是创建表后再到表的可视化设计器中为列加上说明，这样操作起来就相当麻烦了，本篇我们主要讨论如何利用SQL在创建表时为列加上说明信息。

我们先创建一个测试表：

if exists(select 1 from sys.tables where object_id=object_id('test'))
begin
drop table test
end
create table test
(
col1 varchar(50),
col2 varchar(50)
)

这个我们已经很熟悉了，那么怎么为列col1及col2加上说明信息呢？
这就要用到系统存储过程sp_addextendedproperty。
在添加之前我们先看一下sp_addextendedproperty的语法：

sp_addextendedproperty
[ @name = ] { 'property_name' }
[ , [ @value = ] { 'value' }
[ , [ @level0type = ] { 'level0_object_type' }
, [ @level0name = ] { 'level0_object_name' }
[ , [ @level1type = ] { 'level1_object_type' }
, [ @level1name = ] { 'level1_object_name' }
[ , [ @level2type = ] { 'level2_object_type' }
, [ @level2name = ] { 'level2_object_name' }
]
]
]
]

该存储过程一共有8个参数，估计初学者一看就晕了，不要着急，我们可以通过实例来理解，下面我们先利用sp_addextendedproperty为col1列加上说明：

execute sp_addextendedproperty N'MS_Description',N'这是测试列1',N'SCHEMA',N'dbo',N'table',N'test',N'column',N'col1'

上面就是为列col1加上说明的SQL命令，来理解一下:
虽然sp_addextendedproperty有8个参数，但我们可以把这个8个参数理解为4对，其实看参数名我们也可以猜出大概了:
@name与@value为一对
@level0type与@level0name为一对
@level1type与@level1name为一对
@level2type与@level2name为一对

那么这4对参数分别代表什么呢？
1,@name与@value
@name：指定我们是要为列添加什么信息，比如我们要为列添加扩展信息，那么@name就等于'Caption',本篇是要为列添加说明信息，所以@name等于'MS_Description'
@value：指定与@name关联的值，本篇也就是列的具体说明。

2,@level0type与@level0name
@level0type：指定我们要修改的列的表所于那个数据库架构，所以它等于'SCHEMA'，有些网上教程中会说它也可以等于'user'，但在sql server的未来版本中，将删除'user',所以推荐大家用'SCHEMA'
@level0name：指定我们要修改的表所在架构的名称

3,@level1type与@level1name
@level1type：指明我们要修改的列所属对象是表，还是视图等。本篇是修改表中的列，所以为'table',
@@level1name: 指明要修改的列所属表的名称

4,@level2type与@level2name
@level2type：指明我们要修改的对象是列，还是主键，还是约束等。本篇修改的是列,所以为'column'
@level2name：指明要修改列的列名

到此，我们应该了解sp_addextendedproperty中各参数的意思了，完整的SQL命令如下：

if exists(select 1 from sys.tables where object_id=object_id('test'))
begin
drop table test
end
create table test
(
col1 varchar(50),
col2 varchar(50)
)

execute sp_addextendedproperty N'MS_Description',N'这是测试列1',N'SCHEMA',N'dbo',N'table',N'test',N'column',N'col1'

execute sp_addextendedproperty N'MS_Description',N'这是测试列2',N'SCHEMA',N'dbo',N'table',N'test',N'column',N'col2'

执行命令后通过如下查询语句来验证我们的结果：

select B.Name,A.value from sys.extended_properties A
inner join sys.columns B on A.major_id=B.object_id
and A.minor_id=B.column_id
where A.major_id=object_id('test')

关于如何利用SQL语句查询指定表的列说明信息，请参看本站：
利用SQL语句查询出指定表的所有扩展属性(列说明)
执行，返回的结果如下：
Name value
col1 这是测试列1
col2 这是测试列2

创建表时同时为表中的列添加说明信息我们已经完成了，那么，我们要如何利用SQL来修改列的说明信息呢？利用系统存储过程sp_updateextendedproperty，它的用法和sp_addextendedproperty一样，就不多加说明了，修改示例如下：

execute sp_updateextendedproperty N'MS_Description',N'这是修改后的测试列1',N'SCHEMA',N'dbo',N'table',N'test',N'column',N'col1'

execute sp_updateextendedproperty N'MS_Description',N'这是修改后的测试列2',N'SCHEMA',N'dbo',N'table',N'test',N'column',N'col2'

同样利用上面的SQL语句查询修改后的结果：
Name value
col1 这是修改后的测试列1
col2 这是修改后的测试列2

本文来源于:http://www.lmwlove.com/ac/ID666

...全文