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;



CREATE PROCEDURE dbo.usp_fillAllDescriptions





EXEC   usp_fillDescriptions 'T_STATUS','STATUS_ID'

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

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




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----






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: 




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

    DROP PROCEDURE dbo.usp_fillDescriptions;



CREATE  PROCEDURE dbo.usp_fillDescriptions


          @Table nvarchar(50) ,

          @Column nvarchar(50) ,

          @Descr nvarchar(50) = '',

          @Schema nvarchar(50) = 'dbo'



DECLARE @Select nvarchar (500)   

DECLARE @Update nvarchar (500)

DECLARE @Add nvarchar (500)      

DECLARE @All nvarchar (2000)      


          if (ltrim(rtrim(@Descr)) = '')


                   set @Descr = @Column      


          set @Select = ' SELECT name FROM ' +

                   ' ::fn_listextendedproperty (default,''schema'',''' +

                   @Schema + ''', ''table'', ''' +

                   @Table + ''', ''column'', ''' +

                   @Column + ''') where name=''MS_DESCRIPTION'' '


          set @Update =

                     'EXEC sp_updateextendedproperty ''MS_Description'','''+

                   @Descr + ''',''schema'','''+ @Schema +''',''table'','''+

                   @Table + ''', ''column'',''' +  @Column + ''' '


          set @Add = 'EXEC sp_addextendedproperty ''MS_Description'','''+

                   @Descr + ''',''schema'','''+ @Schema +''',''table'','''+

                   @Table + ''',''column'',''' + @Column + ''' '


          set @All = ' IF EXISTS (' + @Select + ') ' +

                   ' begin ' +

                             @Update +

                   ' end ' +

                   ' else ' +

                   ' begin ' +

                             @Add +

                   ' end ' 


          exec sp_executesql @All





Now, with only one line of code (such as: execute dbo.usp_fillAllDescriptions), you can create/recreate all your descriptions for all your tables (for example, I have about 100 tables with three to twenty columns).


Good luck in programming!