about annotations in SQL Server databases---the use of extended properties

The problems encountered in the work, in order to maintain the projects that others do, need to know the unfamiliar project database, often the data storage table naming can not be named to guess the meaning of the table, it seems more laborious, relative to the program code in the comment database of the table is also a comment place? So I searched the web for comments about the table, and found some articles, one of which is to use the extended properties of the table, by adding extended properties to the table for comment description.

This method mainly uses the system stored procedure sp_addextendedproperty to add extended attributes to the table to add the comment information.

The following tests Allow column fields, views, stored procedures, and custom functions in the database to add annotation information by adding extended properties.

The following is the sample code:

-------------------System Stored procedure sp_addextendedproperty code--------------------------

CREATE PROCEDURE Sys.sp_addextendedproperty
@name sysname,
@value sql_variant = NULL,
@level0type varchar = NULL,
@level0name sysname = NULL,
@level1type varchar = NULL,
@level1name sysname = NULL,
@level2type varchar = NULL,
@level2name sysname = NULL
As

DECLARE @ret int

If Datalength (@value) > 7500
Begin
RAISERROR (15097,-1,-1)
Return 1
End

If @name is null
Begin
RAISERROR (15600,-1,-1, ' sp_addextendedproperty ')
Return (1)
End

Execute @ret = sys.sp_validname @name
if (@ret <> 0)
Begin
RAISERROR (15600,-1,-1, ' sp_addextendedproperty ')
Return (1)
End


BEGIN TRANSACTION

Begin
EXEC%%extendedpropertyset (). AddValue (Name = @name, Value = @value, Level0type = @level0type, Level0name = @level0name, Level1Type = @level1type, Level 1name = @level1name, Level2type = @level2type, level2name = @level2name)
IF @ @error <> 0
Begin
COMMIT TRANSACTION
Return (1)
End
End

COMMIT TRANSACTION
Return (0)

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

--Examples of annotations:

Use Test
--Add annotations to the table with extended attributes
EXEC Sys.sp_addextendedproperty
@name = N ' table comment '
, @value =n ' This is a data storage table for the test library. '
, @level0type = N ' schema '
, @level0Name =n ' dbo '
, @level1type = N ' table '
, @level1name = N ' Data '

--Add comments to the view with extended attributes
EXEC Sys.sp_addextendedproperty
@name = N ' view comment '
, @value =n ' This is an example of a view annotation. '
, @level0type = N ' schema '
, @level0Name =n ' dbo '
, @level1type = N ' View '
, @level1name = N ' View_show_chineselanguage '

--add annotations to stored procedures with extended attributes
EXEC Sys.sp_addextendedproperty
@name = N ' stored procedure comment '
, @value =n ' This is an example of a stored procedure comment. '
, @level0type = N ' schema '
, @level0Name =n ' dbo '
, @level1type = N ' procedure '
, @level1name = N ' Pro_analyzeresult '

--add comments to the function with extended attributes
EXEC Sys.sp_addextendedproperty
@name = N ' function comment '
, @value =n ' This is an example of a function comment. '
, @level0type = N ' schema '
, @level0Name =n ' dbo '
, @level1type = N ' function '
, @level1name = N ' Fun_analyzeresult '

Use of extended properties for comments---in SQL Server databases