SQL 数据库中的表的字段说明和表说明

--取SQL 数据库中的表的字段说明和表说明
--http://social.technet.microsoft.com/Forums/zh-CN/sqlserverzhchs/thread/2a90ca5d-85b8-4597-b72b-94762c2dcac7
--http://msdn.microsoft.com/zh-cn/library/ms180047.aspx
--表及字段描述信息处理示例

--创建表
USE [pratice]
GO
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','字段1','user',dbo,'table','表','column',a1

--显示表的描述属性
--如果返回的表为空，可能对象没有扩展属性或用户不具有列出对象扩展属性的权限。当返回数据库本身的扩展属性时，objtype 和 objname 列将为 NULL
SELECT *
FROM ::fn_listextendedproperty (NULL, 'user', 'dbo', 'table', '表', 'column', NULL)

--删除表中列a1的描述属性：
EXEC sp_dropextendedproperty 'MS_Description','user',dbo,'table','表','column',a1

--删除测试
drop table 表

EXEC [sys].[sp_addextendedproperty] @name = 'MS_Description', -- sysname
@value ='测试表' , -- sql_variant
@level0type = 'schema', -- varchar(128)
@level0name = 'dbo', -- sysname
@level1type = 'table', -- varchar(128)
@level1name = 'test' -- sysname
-- @level2type = '', -- varchar(128)
-- @level2name = NULL -- sysname

SELECT * FROM [sys].[fn_listextendedproperty]()
--如果 property_name 的值为 NULL 或默认值，则 fn_listextendedproperty 将返回指定对象的所有属性。
--
--如果指定了对象类型，并且对应的对象名的值为 NULL 或默认值，则 fn_listextendedproperty 将返回指定类型的所有对象的所有扩展属性。
--
--对象是按级别区分的，级别 0 为最高，级别 2 为最低。如果指定了较低级别的对象（级别 1 或级别 2）的类型和名称，则父对象类型和名称应当为 NULL 或默认值以外的给定值。否则，此函数返回空结果集。

--A. 显示数据库的扩展属性
--
--以下示例显示为数据库对象本身设置的所有扩展属性。
USE [pratice];
GO
SELECT objtype, objname, name, value
FROM fn_listextendedproperty(default, default, default, default, default, default, default);
GO

--
--B. 显示表中所有列的扩展属性
--
--以下示例列出 ScrapReason 表中各列的扩展属性。这包含在架构 Production 中。

USE [pratice];
GO
SELECT objtype, objname, name, value
FROM fn_listextendedproperty (NULL, 'schema', 'Production', 'table', 'test', 'column', default);
GO
--
--C. 显示架构中所有表的扩展属性
--
--以下示例列出了在 Sales 架构中所包含的所有表的扩展属性。

USE [pratice];
GO
SELECT objtype, objname, name, value
FROM fn_listextendedproperty (NULL, 'schema', 'Sales', 'table', default, NULL, NULL);
GO

 

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

--引用某个级别中的对象必须用拥有或包含它们的更高级别对象的名称进行限制。 例如，当将扩展属性添加到表列（级别 2）时，还必须指定包含该列的表名（级别 1）以及包含该表的架构（级别 0）。
--
--如果所有对象类型和名称都为空，则属性属于当前数据库本身。
--
--对于系统对象、用户定义数据库的作用域以外的对象或者未在 Arguments 中作为有效输入列出的对象，不允许使用扩展属性。

--
--A.将扩展属性添加到数据库中
--
--以下示例将值为 'AdventureWorks2012 Sample OLTP Database' 的属性名称 'Caption' 添加到 AdventureWorks2012 示例数据库中。

USE AdventureWorks2012;
GO
--Add a caption to the AdventureWorks2012 Database object itself.
EXEC sp_addextendedproperty
@name = N'Caption',
@value = 'AdventureWorks2012 Sample OLTP Database';

--B.将扩展属性添加到表中的列
--
--以下示例将标题属性添加到 Address 表中的 PostalCode 列。

USE AdventureWorks2012;
GO
EXEC sp_addextendedproperty
@name = N'Caption',
@value = 'Postal code is a required column.',
@level0type = N'Schema', @level0name = 'Person',
@level1type = N'Table', @level1name = 'Address',
@level2type = N'Column', @level2name = 'PostalCode';
GO

 

--C.将输入掩码属性添加到列中
--
--以下示例将输入掩码属性 '99999 or 99999-9999 or #### ###' 添加到 Address 表中的 PostalCode 列。

USE AdventureWorks2012;
GO
EXEC sp_addextendedproperty
@name = N'Input Mask ', @value = '99999 or 99999-9999 or #### ###',
@level0type = N'Schema', @level0name = 'Person',
@level1type = N'Table', @level1name = 'Address',
@level2type = N'Column',@level2name = 'PostalCode';
GO

--D.将扩展属性添加到文件组中
--
--下面的示例向 PRIMARY 文件组添加了一个扩展属性。

USE AdventureWorks2012;
GO
EXEC sys.sp_addextendedproperty
@name = N'MS_DescriptionExample',
@value = N'Primary filegroup for the AdventureWorks2012 sample database.',
@level0type = N'FILEGROUP', @level0name = 'PRIMARY';
GO

--E.将扩展属性添加到架构中
--
--下面的示例向 HumanResources 架构添加了一个扩展属性。

USE AdventureWorks2012;
GO
EXECUTE sys.sp_addextendedproperty
@name = N'MS_DescriptionExample',
@value = N'Contains objects related to employees and departments.',
@level0type = N'SCHEMA',
@level0name = 'HumanResources';

--F.将扩展属性添加到表中
--
--下面的示例将扩展属性添加到 Person 架构中的 Address 表。

USE AdventureWorks2012;
GO
EXEC sys.sp_addextendedproperty
@name = N'MS_DescriptionExample',
@value = N'Street address information for customers, employees, and vendors.',
@level0type = N'SCHEMA', @level0name = 'Person',
@level1typ

--G.将扩展属性添加到角色中
--
--下面的示例创建了一个应用程序角色并向该角色添加了一个扩展属性。

USE AdventureWorks2012;
GO
CREATE APPLICATION ROLE Buyers
WITH Password = '987G^bv876sPY)Y5m23';
GO
EXEC sys.sp_addextendedproperty
@name = N'MS_Description',
@value = N'Application Role for the Purchasing Department.',
@level0type = N'USER',
@level0name = 'Buyers';

 

--H.将扩展属性添加到类型中
--
--下面的示例向类型添加了一个扩展属性。

USE AdventureWorks2012;
GO
EXEC sys.sp_addextendedproperty
@name = N'MS_Description',
@value = N'Data type (alias) to use for any column that represents an order number. For example a sales order number or purchase order number.',
@level0type = N'SCHEMA',
@level0name = N'dbo',
@level1type = N'TYPE',
@level1name = N'OrderNumber';

--I.向用户添加扩展属性
--
--下面的示例创建了一个用户并向该用户添加了一个扩展属性。

USE AdventureWorks2012;
GO
CREATE USER CustomApp WITHOUT LOGIN ;
GO
EXEC sys.sp_addextendedproperty
@name = N'MS_Description',
@value = N'User for an application.',
@level0type = N'USER',
@level0name = N'CustomApp';