Extended Properties in SQL Server

Extended properties (EP) are SQL Server objects which can be used for documenting other database objects such as tables, views, functions etc. Extended properties offer a common standard place for information about various database objects

Extended properties have three level categories such as Schema -> Table -> Column.  Adding, altering of extended properties are handled through system stored procedures  

sp_addextendedproperty  -> adds a new extended property to a database object

sp_dropextendedproperty -> removes an extended property from a database object

sp_updateextendedproperty -> updates the value of an existing extended property

The above stored procedures have the following parameters

@name = Name of the extended property  

@value = Comments/Description

@level0type = Schema, @level0name = name of schema (dbo)

@level1type = Table,  @level1name = table name

@level2type = Column, @level2name = column name

The Level0/1/2Type’s are not limited to Schema/Table/Column, refer to http://blogs.lessthandot.com/index.php/datamgmt/datadesign/document-your-sql-server-databases/, for a detailed explanation

Consider a sample schema,

create table dbo.Consumers

(

            SeqID int identity(1,1) not null,

            Name varchar(100) not null,

)

To add a description for the SeqID column we can use ‘sp_addextendedproperty’ system stored procedure

-- creating

exec sp_addextendedproperty

@name = N'MS_Description',

@value = 'Sequence ID to identify consumers, auto incrementing number ',

@level0type = N'Schema', @level0name = dbo,

@level1type = N'Table',  @level1name = Consumers,

@level2type = N'Column', @level2name = SeqID;

-- altering

exec sp_updateextendedproperty

@name = N'MS_Description',

@value = N' Sequence ID to identify consumers, auto incrementing number seed value 1’,

@level0type = N'Schema', @level0name = dbo,

@level1type = N'Table',  @level1name = Consumers,

@level2type = N'Column', @level2name = SeqID;

-- dropping

EXEC sp_dropextendedproperty

@name = N'MS_Description',

@level0type = N'Schema', @level0name = dbo,

@level1type = N'Table',  @level1name = Consumers,

@level2type = N'Column', @level2name = SeqID

The best use of extended properties is getting the column definitions of a table. I always create the following sql query as a stored procedure and filter it by table name

-- viewing

select col.name as [Column], ep.value as  [Description]

from sys.tables tab inner join sys.columns col

on tab.object_id = col.object_id left join sys.extended_properties ep

on tab.object_id = ep.major_id

and col.column_id = ep.minor_id

and ep.name = 'MS_Description'

where tab.name = @TabName