Managing Extended Properties

In this section we will show some examples on how to work with extended properties in SQL Server. We will add, update, query, and drop extended properties.

For demonstration purposes we first create a simple table in SQL Servers' temp database:

USE TempDB GO IF object_id('MyTable') > 0 DROP TABLE MyTable GO CREATE TABLE MyTable ( i1 INT NOT NULL CONSTRAINT pk_MyTable PRIMARY KEY, c1 VARCHAR(10) NOT NULL) GO

In the above batch we've created a small base table named 'MyTable' which has two columns: i1 and c1.

`Adding an Extended Property`

You add an Extended Property for a given database object by using the sp_addextendedproperty stored procedure. Suppose, we want to create an Extended Property for column i1. We can do so by using the following procedure call:

EXEC sp_addextendedproperty 'Caption' , 'My Custom value' , 'user' , dbo , 'table' , 'MyTable' , 'column' , i1

We used 'Caption' label as an extended property name in our example. Enterprise Manager and SQL Server Management Studio use 'MS_Description' label by default. For more information how this situation can be handled in ApexSQL Doc see here.

The complete syntax for the use of sp_addextendedproperty can be found in the Microsoft SQL Server Books Online (BOL), so we won't explain it here. Just one note in this context: 'My Custom Value' is of type sql_variant and as such can store values of almost any other base type that SQL Server supports with the exception of (N)TEXT, IMAGE, TIMESTAMP and SQL_VARIANT itself. The maximum length for the value is 7,500 bytes.

The above execution of sp_addextendedproperty adds a property named 'Caption' for the column i1 of the table MyTable. The property now has the value 'My Custom Value'. Note that although one single column can have multiple Extended Properties, the name for each property must be unique. When you try to execute the above procedure call a second time, ceteris paribus, SQL Server raises an error:

Server: Msg 15233, Level 16, State 1, Procedure sp_addextendedproperty, Line 48

Property cannot be added. Property 'Caption' already exists for 'dbo.MyTable.i1'.

Now that you have your Extended Property created, there are 3 other things you can do with that property. You can query it, update it or drop it.

`Query for Extended Properties`

To query for extended properties SQL Server offers the sys.extended_properties system view (in SQL Server 2005) or the fn_listextendedproperty function (in SQL Server 2000).

So, in SQL Server 2000 you may query your properties using following sample:

SELECT * FROM ::fn_listextendedproperty (NULL,'user','dbo','table','MyTable','column',DEFAULT)

Where the parameter list is:

property name (NULL, for querying all available extended properties)
level0_object_type (‘user’)
level0_object name (‘dbo’, our table belongs to user ‘dbo’)
level1_object_type (‘table’, points that we query the properties of the table)
level1_object name (‘MyTable’, our table name)
level2_object_type (‘column’, points that we query the properties of the table column)
level2_object name (DEFAULT, we don’t point here a particular column name)

After executing of the above statement you get:

SQL Server 2005 offers additional and easier way in viewing your properties, so you may use this example to achieve the same result as above:

SELECT * FROM sys.extended_properties WHERE major_id = Object_id('MyTable')

which returns

`Update an Extended Property`

To update an extended property SQL Server offers the sp_updateextendedproperty stored procedure. Suppose you want to change the value of our property 'MyCustomValue' to 'SomeMoreMeaningful'. The EXECUTE call looks like this:

EXEC sp_updateextendedproperty 'Caption' , 'SomeMoreMeaningful' , 'user' , dbo , 'table' , 'MyTable' , 'column' , i1

Now querying the property we may see that it has actually been changed:

`Drop an Extended Property`

Finally, to drop the Extended Property 'caption' for the column i1 of the table MyTable, you use the sp_dropextendedproperty stored procedure like this:

EXEC sp_dropextendedproperty 'Caption' , 'user' , dbo , 'table' , 'MyTable' , 'column' , i1

Now querying the property we see that no data is returned anymore:

Note, that while you can manually drop Extended Properties, dropping a given database object will automatically drop all maybe existing Extended Properties for that object as well.