SQL Server extended properties (comments)

Most DBMSs allow to read and write comments about tables, columns and other database objects. SQL Server has a more complex feature called Extended Properties.

What extended properties are

Whereas a comment is a single short text describing a database object, an extended property is a labeled information describing one aspect of a database object.

Objects can have zero or more extended properties. The choice of which properties to use is up to us. For example, for a table we may want to have these extended properties: owner; application; notes. For a database user, we may want to have extended properties like: department; email; phone. But again, these are just examples.

Let’s see how to perform the CRUD operations on extended properties.

Creating extended properties

If an object doesn’t have a certain extended property, we can add it in this way:

EXEC sp_addextendedproperty
@name        = N'owner',   @value = 'John Smith',
@level0type  = N'Schema',  @level0name = 'catalog',
@level1type  = N'Table',   @level1name = 'user';
GO

EXEC sp_addextendedproperty
@name        = N'comment',  @value = 'Mobile app users',
@level0type  = N'Schema',   @level0name = 'catalog',
@level1type  = N'Table',    @level1name = 'user';
GO

Reading extended properties

To list existing extended properties related to an object:

SELECT *
    FROM fn_listextendedproperty(NULL, 'schema', 'ecommerce', 'table', 'catalog');
GO

Updating extended properties

We can change existing properties in this way:

EXEC sp_addextendedproperty
@name        = N'owner',   @value = 'Simone Weil',
@level0type  = N'Schema',  @level0name = 'catalog',
@level1type  = N'Table',   @level1name = 'user';
GO

Deleting extended properties

To delete an extended property:

EXEC sp_dropextendedproperty
@name        = N'comment',
@level0type  = N'Schema',   @level0name = 'catalog',
@level1type  = N'Table',    @level1name = 'user';
GO

Reference

More articles: