extended property

最新推荐文章于 2021-12-22 19:09:59 发布

转载最新推荐文章于 2021-12-22 19:09:59 发布 · 130 阅读

0 ·

CC 4.0 BY-SA版权

原文链接：http://www.cnblogs.com/stswordman/archive/2010/07/21/1781937.html

本文详细介绍了如何在 SQL Server 中创建、查询、修改和删除扩展属性，包括为模式、表和列添加描述信息的方法。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

http://msdn.microsoft.com/en-us/library/ms180047.aspx

create schema schema1

go

select * from sys.extended_properties where major_id = schema_id( ' schema1 ' )

go

-- create an extented property for schema1

EXEC sp_addextendedproperty

@name = N ' schemaDescription ' , @value = ' this is schema1, uses to test ' ,

@level0type = N ' SCHEMA ' , @level0name = N ' schema1 ' ;

go

go

select * from sys.extended_properties where major_id = schema_id( ' schema1 ' )

go

create table schema1.ta(c1 int ,c2 int )

go

create table schema1.tb(c1 int ,c2 int )

go

-- create an extented property for schema1.ta

EXEC sp_addextendedproperty

@name = N ' table description ' , @value = ' this is a table under schema schema1 ' ,

@level0type = N ' SCHEMA ' , @level0name = N ' schema1 ' ,

@level1type = N ' table ' , @level1name = N ' ta ' ;

go

select * from sys.extended_properties where major_id = object_id ( ' schema1.ta ' )

go

EXEC sp_addextendedproperty

@name = N ' table111 description ' , @value = ' this is a table1111 under schema schema1 ' ,

@level0type = N ' SCHEMA ' , @level0name = N ' schema1 ' ,

@level1type = N ' table ' , @level1name = N ' tb ' ;

go

-- create an extented property for column c1 of table schema1.ta

EXEC sp_addextendedproperty

@name = N ' column description ' , @value = ' this is a column description ' ,

@level0type = N ' SCHEMA ' , @level0name = N ' schema1 ' ,

@level1type = N ' table ' , @level1name = N ' ta ' ,

@level2type = N ' column ' , @level2name = N ' c1 ' ;

GO

EXEC sp_addextendedproperty

@name = N ' column description ' , @value = ' this is a column2 description ' ,

@level0type = N ' SCHEMA ' , @level0name = N ' schema1 ' ,

@level1type = N ' table ' , @level1name = N ' ta ' ,

@level2type = N ' column ' , @level2name = N ' c2 ' ;

GO

select * from sys.extended_properties where major_id = object_id ( ' schema1.ta ' )

go

go

SELECT objtype, objname, name, value

FROM fn_listextendedproperty ( NULL , ' schema ' , ' schema1 ' , ' table ' , default , null , default );

go

SELECT objtype, objname, name, value

FROM fn_listextendedproperty ( NULL , ' schema ' , ' schema1 ' , ' table ' , ' ta ' , ' column ' , default );

转载于:https://www.cnblogs.com/stswordman/archive/2010/07/21/1781937.html