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:
USE MICLIV;
GO
IF OBJECT_ID ('[dbo].[usp_fillDescriptions]', 'P' ) IS NOT NULL
DROP PROCEDURE dbo.usp_fillDescriptions;
GO
-------------------------------------------------------------
CREATE PROCEDURE dbo.usp_fillDescriptions
(
@Table nvarchar(50) ,
@Column nvarchar(50) ,
@Descr nvarchar(50) = '',
@Schema nvarchar(50) = 'dbo'
)
AS
DECLARE @Select nvarchar (500)
DECLARE @Update nvarchar (500)
DECLARE @Add nvarchar (500)
DECLARE @All nvarchar (2000)
BEGIN
if (ltrim(rtrim(@Descr)) = '')
begin
set @Descr = @Column
end
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
END
GO
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!