Overview of Sql server extended properties Asp.Net C#

Posted by james on June 15, 2011 Leave a comment (0) Go to comments

SQL Server extended properties have actually been around for a long time. And when you look at how SQL Server manages its core meta data, the fact that a facility exists to manage your own meta data shouldn’t be all that surprising. Before diving too far into the examples, let’s take a quick inventory of the tools used to illustrate the concepts:

Visual Studio 2010
SQL Server 2008 (any version of SQL Server will work)
NUnit NetProgHelp Framework (v. 2.5.8, download at nunit.org)
With our tools in place, let’s focus on what SQL Server gives us for free. All extended properties are stored in the sys.extended_properties table. The sys.extended_properties table contains the following columns:

class (tinyint): Examples are database: 0, column: 1, parameter: 2 – default is 0.
class_desc (nvarchar(60): Examples are DATABASE, OBJECT_OR_COLUMN, PARAMETER – default is DATABASE.
major_id (int): ID of the object to which the property applies – default is 0.
minor_id (int): Secondary ID of the object to which the property applies – default is 0. When major_id is 0, minor_id is 0.
name (sysname: nvarchar(128)): Unique name for property.
value (sql_variant): Value of the property.
In many cases, the only data elements you need to be concerned with are name and value. As with all system meta-data tables, the SQL login you use must have the necessary select and execute privileges. Like other system meta-data tables, you don’t need to interact with the table directly. There are several system stored procedures that handle adding, removing and updating extended properties:

sp_addextendedproperty
sp_updateextendedproperty
sp_removeextendedproperty
When you work with these stored procedures, you don’t have to worry about data defaults and data validation because the stored procedures do all of the work for you.

USE NetProgHelp;
GO
 
declare @DBVersion as sql_variant;
select @DBVersion as nonExistentBeforeCreationVersion
 
EXEC sys.sp_addextendedproperty
@name = N'Version',
@value = N'1.0.0';
 
select @DBVersion = value
   from sys.extended_properties
   where name = 'Version';
 
select @DBVersion as newVersion
 
EXEC sp_updateextendedproperty
    @name = N'Version'
    ,@value = '2.0.0';
 
Set @DBVersion = null;   
 
select @DBVersion = value
   from sys.extended_properties
   where name = 'Version';
 
select @DBVersion as updatedVersion
 
EXEC sp_dropextendedproperty
     @name = 'Version';
 
Set @DBVersion = null;
 
select @DBVersion = value
   from sys.extended_properties
   where name = 'Version';
 
select @DBVersion as nonExistentAfterDropVersion