表及字段描述信息处理示例

将新扩展属性添加到数据库对象中。

 

Transact-SQL 语法约定

 

语法

 

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' }

                        ]

                ]

        ]

    ] 

 

备注

为了指定扩展属性，SQL Server 数据库中的对象分为三个级别（、和 2）。级别 0 是最高级别，定义为包含在数据库作用域中的对象。级别 1 的对象包含在架构作用域或用户作用域中，而级别 ２的对象包含在级别 1 对象中。可以为这些级别中任一级别的对象定义扩展属性。

 

引用某个级别中的对象必须用拥有或包含它们的更高级别对象的名称进行限制。例如，当将扩展属性添加到表列（级别 2）时，还必须指定包含该列的表名（级别 1）以及包含该表的架构（级别 0）。

 

有关对象及其有效的级别 0、和 2 类型的完整列表，请参阅对数据库对象使用扩展属性。

 

如果所有对象类型和名称都为空，则属性属于当前数据库本身。

 

对于系统对象、用户定义数据库的作用域以外的对象或者未在 Arguments 中作为有效输入列出的对象，不允许使用扩展属性。

 

架构与用户

在 SQL Server 的早期版本中，用户拥有表、视图和触发器之类的数据库对象。因此，允许将扩展属性添加到这些对象之一以及将用户名指定为级别 0 类型。但在 SQL Server 2005 中，数据库对象包含在架构中。它们独立于拥有架构的用户。

 

在 SQL Server 2005 中，我们建议不要在将扩展属性应用于数据库对象时指定 USER 作为级别 0 类型，因为这会导致名称解析不明确。例如，假定用户 Mary 拥有两个架构（Mary 和 MySchema），并且这两个架构都包含名为 MyTable 的表。如果 Mary 将扩展属性添加到表 MyTable 并指定 @level0type = N'USER'、@level0name = Mary，则扩展属性应用于哪个表并不明确。为了保持向后兼容，SQL Server 将属性应用于名为 Mary 的架构所包含的表。有关用户与架构的详细信息，请参阅用户架构分离。

 

参数

[ @name ] = { 'property_name' }

要添加的属性的名称。property_name 的数据类型为 sysname，不能为 NULL。名称还可以包括空格或非字母数字字符串以及二进制值。

 

[ @value = ] { '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 和 NULL。

 

重要事项：

作为级别 0 类型的 USER 和 TYPE 将在 SQL Server 的未来版本中删除。请避免在新的开发工作中使用这些功能，并考虑修改当前使用这些功能的应用程序。改用 SCHEMA 代替 USER 作为级别 0 类型。对于 TYPE，请使用 SCHEMA 作为级别 0 类型，使用 TYPE 作为级别 1 类型。

 

 

 

[ @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、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（失败）

 

 

 

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'0:取消,1:已到,2:未到,3:预订违约' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'Tab_Passenger', @level2type=N'COLUMN', @level2name=N'PassengerState'

 

 

-表及字段描述信息处理示例

 

--创建表

create table 表(a1 varchar(10),a2 char(2))

 

--为表添加描述信息

EXECUTE sp_addextendedproperty N'MS_Description', '人员信息表', N'user', N'dbo', N'table', N'表', NULL, NULL

 

--为字段a1添加描述信息

EXECUTE sp_addextendedproperty N'MS_Description', '姓名', N'user', N'dbo', N'table', N'表', N'column', N'a1'

 

--为字段a2添加描述信息

EXECUTE sp_addextendedproperty N'MS_Description', '性别', N'user', N'dbo', N'table', N'表', N'column', N'a2'

 

--更新表中列a1的描述属性：

EXEC sp_updateextendedproperty 'MS_Description','字段','user',dbo,'table','表','column',a1

 

--删除表中列a1的描述属性：

EXEC sp_dropextendedproperty 'MS_Description','user',dbo,'table','表','column',a1

 

--删除测试

drop table 表

 

 

 

 

--SQL2005:

 

可以通过以下语句获得所有描述信息:

 

--------------------------------------------------

 

 

SELECT t.name AS [表名]

      ,c.name AS [字段名]

      ,ep.value AS [字段说明]

FROM sys.tables AS t

    INNER JOIN sys.columns AS c

        ON t.object_id = c.object_id

    LEFT JOIN sys.extended_properties AS ep

        ON ep.major_id = c.object_id AND ep.minor_id = c.column_id

WHERE ep.class = 1 --条件省略可以查看全部字段说明，否则只显示有说明的字段

   --AND t.name='表名' --省略这个条件可以显示全表

 

-----------------------------------------------------

 

--SQL2000:

 

------------------------------------------------------

 

 

SELECT OBJECT_NAME(a.id) [表名], a.name [字段名], b.value [字段说明]

FROM syscolumns a

    LEFT JOIN sysproperties b

        ON  a.id = b.id

            AND a.colid = b.smallid

--WHERE a.ID = OBJECT_ID('表名') --省略这个条件可以显示所有表

--------------------------------------------------------