This article defines SQL Server extended properties. We discuss adding, editing and deleting them. In the next article we discuss how to view them using a query.

What are extended properties in SQL Server? Extended properties are additional customized properties to store additional information in a SQL Server database. This can be very useful for documenting a database and its objects. Perhaps you want to document the version number of the database or provide a description of a table or a stored procedures. Perhaps you have a series of stored procedures that operate in a specific order and can be divided into sections or “phases”. You could document the stored procedures with a key-value pair of Phase and a number. Here is a list of some important things to know about extended properties.

• We can store text such as descriptive or instructional content as properties of the database object.
• Multiple extended properties can be added to a single object.
• Every Extended Property has a user defined name and value (key-value pair).
• For specifying extended properties, the objects in a SQL Server database are classified into three levels, 0, 1, and 2.
• The value of Extended Properties is a SQL variant and can contain up to 7500 bytes of data.
• They are backed up with the database, and scripted out with the build scripts.
• When you need to retrieve the information, you simply query it.
• Extended properties have been available since at least SQL Server 2000 in all editions.

In SSMS you can view the extended properties of the database by right-clicking the name of the database in the Object Explorer of SSMS, click Properties and then select the Extended Properties page in the upper left part of the screen. Earlier I created a database called Gruber. It is named after the SQL author Martin Gruber who many years ago wrote a book called Understanding SQL.

Here is what the top part of the extended properties GUI window looks like for a database without any extended properties added.

Adding an Extended Property to the Database

Here is the code to add an extended property to the database.

use Gruber
go
EXEC sp_addextendedproperty 
@name = N'Purpose', 
@VALUE = 'Martin Grubers database containing Salespeople Customers and Orders.';

Here is the code to view all of the extended properties in a database.

SELECT * FROM sys.extended_properties

Here is what SSMS shows after running the above SELECT statement.

Here is what the SSMS GUI shows for the extended properties of the database.

Levels

In the sql code above where we added an extended property to the database itself, we did not use any levels. We only use the @name and @value parameters. There are no level types or names.

EXECUTE sys.sp_addextendedproperty 
@name = N'Purpose',
@VALUE = N'Holds info for dbo.',
    @level0type = N'SCHEMA', 
    @level0name = 'dbo';

EXECUTE sys.sp_addextendedproperty 
@name = N'Purpose',
@VALUE = N'Holds Salespeople data in a table.',
    @level0type = N'SCHEMA', 
    @level0name = 'dbo',
        @level1type = N'Table', 
        @level1name = 'Salespeople';

After adding two more for the other 2 tables in our database, we can query the table sys.extended_properties and get the following results in SSMS.

Column in a Table

Here is the code to add an extended property to a column in a table. The table is called Salespeople and the column is called snum.

EXECUTE sys.sp_addextendedproperty 
@name = 'Description', 
@VALUE = 'snum is a unique code to identify a sales person.', 
    @level0type = 'SCHEMA', 
    @level0name = 'dbo', 
        @level1type = 'Table', 
        @level1name = 'Salespeople', 
            @level2type = 'Column', 
            @level2name = 'snum';

Extended Properties Hierarchy

The website Less Than Dot has an article and a diagram showing the Extended Properties Hierarchy of SQL Server. This article was written by Jes Borland who is a Senior SQL Engineer at Concurrency and a Microsoft Data Platform MVP.

Updating Extended Properties

Updating uses very similar code as adding except that we use a different stored procedure: sys.sp_updateextendedproperty. Below is how we update the extended property for the Salespeople table.

EXECUTE sys.sp_updateextendedproperty 
@name = N'Purpose',
@VALUE = N'Holds Sales People data in a table. Each sales person can have multiple customers.',
    @level0type = N'SCHEMA', 
    @level0name = 'dbo',
        @level1type = N'Table', 
        @level1name = 'Salespeople';

Deleting Extended Properties

You can execute sp_dropextendedproperty to delete an extended property.

EXECUTE sys.sp_dropextendedproperty 
@name = N'Purpose',
    @level0type = N'SCHEMA', 
    @level0name = 'dbo',
        @level1type = N'Table', 
        @level1name = 'Salespeople';