Using SQL Server 2005 system SP to change extended property values of database objects

Such kind of the functions and stored procedures as fn_listextendedproperty, sp_addextendedproperty, sp_updateextendedproperty allow to returns extended property values of database objects, adds a new extended property to a database object, updates the value of an existing extended property. We compared, in detail, the system function fn_listextendedproperty of SQL Server 2005 with the similar function of SQL Server and described using the function for .NET application in the articles (1, 2). The stored procedures of SQL 2005 have analogical syntax and differences and I am going to miss "comprehensive" explanations (for details see: sp_addextendedproperty and sp_updateextendedproperty) and to make article of shorter. In this article, that is the logical continue of the earlier published articles articles, I will show how you can update the value of an existing extended property or add a new extended property to a database object with the help of the system stored procedures of SQL 2005. The examples are written using T-SQL.

You remember, that description of the table's columns of our system is very important for our applications and some changes of the descriptions can just damage the interface (according to descriptions we have text of labels, titles of the columns of GridViews, etc.). We have to keep in the mind that there are many reasons "accidentally" to lose all our descriptions (or just to change), which we manually enter the first time (see fig. 1). Indeed, from the beginning of the development till "production", the database, usually, "travels" from "developing" server to "production" throw "preproduction" and, of course, "on the road" we can lose all our descriptions or part of them; or just we have to change hundred descriptions on every server; and so on.

Figure 1:

From this point of view it is better to write text of the descriptions (it is enough to write only one time!) just in some stored procedure (or SQL script) and then, with the help of one click, to restore (or to change) all our descriptions at any time and at any server that we want. I use stored procedure like this:

IF OBJECT_ID ('[dbo].[usp_fillAllDescriptions]', 'P' ) IS NOT NULL

DROP PROCEDURE dbo.usp_fillAllDescriptions;

GO

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

CREATE PROCEDURE dbo.usp_fillAllDescriptions

AS

BEGIN

----T_STATUS----

EXEC usp_fillDescriptions 'T_STATUS','STATUS_ID'

EXEC usp_fillDescriptions 'T_STATUS','STATUS_DES','Status'

EXEC usp_fillDescriptions 'T_STATUS','STATUS_UPDATED','Last Update'

----S_PERSONS----

EXEC usp_fillDescriptions 'S_PERSONS','Person_ID','ID'

EXEC usp_fillDescriptions 'S_PERSONS','FirstName','First Name'

EXEC usp_fillDescriptions 'S_PERSONS','LastName','Last Name'

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

----And so on----

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

END

GO

The stored procedure usp_fillDescriptions should have at least three parameters: name of the table, name of the column and description. The last parameter is optional and if we don't fill it, the description just has the same text as the name of the column. In order to define what kind of the operation we should do (update or add) we will use our old fried fn_listextendedproperty. To update descriptions we will use the sp_updateextendedproperty procedure and to add - sp_addextendedproperty: