SQL Server扩展属性的增、删、改、查

依然饭跑跑

　　使用 sql 语句创建表时，可以使用系统存储过程 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' }   
                         ]   
                 ]  
         ]   
     ]   
[;]


execute sys.sp_addextendedproperty
@name = N'MS_Description',
@value = N'要添加的属性信息',
@level0type = N'Schema',
@level0name = N'dbo',
@level1type = N'table',
@level1name = N'表名',
@level2type = N'column',
@level2name = N'列名'
　　参数：
　　[ @name ] = { 'property_name' }
是要添加的属性的名称。 property_name是sysname和不能为 NULL。 名称还可以包括空格或非字母数字字符串以及二进制值。
　　[ @value= ] { 'value'}
要与属性关联的值。 值是sql_variant，默认值为 NULL。 value 的大小不能超过 7,500 个字节。
　　[ @level0type=] {level0_object_type}
级别 0 对象的类型。 level0_object_type是varchar （128)，默认值为 NULL。
　　有效输入包括：ASSEMBLY、CONTRACT、EVENT NOTIFICATION、FILEGROUP、MESSAGE TYPE、PARTITION FUNCTION、PARTITION SCHEME、REMOTE SERVICE BINDING、ROUTE、SCHEMA、SERVICE、USER、TRIGGER、TYPE、PLAN GUIDE 和 NULL。
　　[ @level0name=] {level0_object_name}
所指定的级别 0 对象类型的名称。 level0_object_name是sysname默认值为 NULL。
　　[ @level1type=] {level1_object_type}
级别 1 对象的类型。 level1_object_type是varchar （128)，默认值为 NULL。
　　有效的输入包括：AGGREGATE、DEFAULT、FUNCTION、LOGICAL FILE NAME、PROCEDURE、QUEUE、RULE、SYNONYM、TABLE、TABLE_TYPE、TYPE、VIEW、XML SCHEMA COLLECTION 和 NULL。
　　[ @level1name=] {level1_object_name}
所指定的级别 1 对象类型的名称。 level1_object_name是sysname，默认值为 NULL。
　　[ @level2type=] {level2_object_type}
级别 2 对象的类型。 level2_object_type是varchar （128)，默认值为 NULL。
　　有效的输入包括：COLUMN、CONSTRAINT、EVENT NOTIFICATION、INDEX、PARAMETER、TRIGGER 和 NULL。
　　[ @level2name=] {level2_object_name}
所指定的级别 2 对象类型的名称。 level2_object_name是sysname，默认值为 NULL。








返回代码值：　　0（成功）或 1（失败）



create table Department
(
     D_ID    int        not null    identity(1,1) primary key,
     D_Name    varchar(50) not null unique,
     D_Url    varchar(200) null ,
     D_ImgUrl    varchar(200) null,
     D_ParentID    int not null default(0),
     D_Remark    varchar(500) null
)
go

execute sys.sp_addextendedproperty N'MS_Description',N'部门表',N'Schema',N'dbo',N'table',N'Department',null,null
go
execute sys.sp_addextendedproperty N'MS_Description',N'部门表ID',N'Schema',N'dbo',N'table',N'Department',N'column',N'D_ID'
execute sys.sp_addextendedproperty N'MS_Description',N'部门名称',N'Schema',N'dbo',N'table',N'Department',N'column',N'D_Name'
execute sys.sp_addextendedproperty N'MS_Description',N'链接地址',N'Schema',N'dbo',N'table',N'Department',N'column',N'D_Url'
execute sys.sp_addextendedproperty N'MS_Description',N'节点图片地址',N'Schema',N'dbo',N'table',N'Department',N'column',N'D_ImgUrl'
execute sys.sp_addextendedproperty N'MS_Description',N'父节点ID',N'Schema',N'dbo',N'table',N'Department',N'column',N'D_ParentID'
execute sys.sp_addextendedproperty N'MS_Description',N'部门描述',N'Schema',N'dbo',N'table',N'Department',N'column',N'D_Remark'
go
　　PS: SQL Server 数据库中的对象分为三个级别：0、1 和 2。 级别 0 是最高级别，2 级为最低。当用户添加、更新或删除扩展属性时，必须指定所有更高级别的对象。例如，当将扩展属性添加到表列（级别 2）时，还必须指定包含该列的表名（级别 1）以及包含该表的架构（级别 0）。
　　为表和字段添加了扩展属性之后，可以使用扩展属性目录视图 sys.extended_properties 查询到所添加的扩展属性。
　　sys.extended_properties 针对当前数据库中的每个扩展属性返回一行。

select * from sys.extended_properties

select * from sys.extended_properties where major_id=object_id('Department')
　　或者还可以使用函数 fn_listextendedproperty 查询扩展属性，fn_listextendedproperty 返回数据库对象的扩展属性值。
　　fn_listextendedproperty 语法：



fn_listextendedproperty (   
     { default | 'property_name' | NULL }   
   , { default | 'level0_object_type' | NULL }   
   , { default | 'level0_object_name' | NULL }   
   , { default | 'level1_object_type' | NULL }   
   , { default | 'level1_object_name' | NULL }   
   , { default | 'level2_object_type' | NULL }   
   , { default | 'level2_object_name' | NULL }   
   )  
　　下面是 fn_listextendedproperty 返回的表的格式。

　　参数同上，使用方法如下：




SELECT * FROM fn_listextendedproperty('MS_Description', 'Schema', 'dbo', 'table', 'Department', 'column', default)

SELECT * FROM fn_listextendedproperty('MS_Description', 'Schema', 'dbo', 'table', 'Department', 'column', 'D_Name')
　　使用 sp_updateextendedproperty 系统存储过程修改扩展属性。
　　sp_updateextendedproperty 语法：



sp_updateextendedproperty  
     [ @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' }  
                      ]  
               ]  
         ]  
     ]  

返回代码值：　　0（成功）或 1（失败）
　　使用方法示例：




execute sp_updateextendedproperty N'MS_Description',N'部门表测试修改',N'Schema',N'dbo',N'table',N'Department',null,null

execute sp_updateextendedproperty N'MS_Description',N'部门详细描述',N'Schema',N'dbo',N'table',N'Department',N'column',N'D_Remark'
　　使用 sp_dropextendedproperty 系统存储过程删除扩展属性。
　　sp_dropextendedproperty  语法：



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

返回代码值：　　0（成功）或 1（失败）
　　使用方法示例：




execute sp_dropextendedproperty N'MS_Description',N'Schema',N'dbo',N'table',N'Department',null,null

execute sp_dropextendedproperty N'MS_Description',N'Schema',N'dbo',N'table',N'Department',N'column',N'D_Remark'
　　扩展属性分很多种，添加表和字段的描述只是其中一种扩展属性，具体请看参考。
　　参考：
　　https://technet.microsoft.com/zh-cn/library/ms180047.aspx