SQL Server extended properties

SQL Server extended properties feature can be very handy in SQL Server because it allows us to store additional information about SQL objects. Developers often need to store information about each SQL Server objects, like information about stored procedures, what those procedures do, etc. which is also a way to create a self-documenting database.

In this article, how to add, modify, and remove SQL Server extended properties using the SQL Server Management Studio’s extended properties feature will be shown. The same process will be shown with the usage of SQL Server stored procedures. At the end of the article, it will be shown how to add information (description) about each database objects using the Extended property editor feature from ApexSQL Search third-party tool.

SQL Server Management Studio (SSMS) Extended Properties

To add SQL Server extended properties via SSMS, in Object Explorer, select an object for which you want to enter a description, right-click on it and, from the context menu, choose the Properties command:

Object Explorer context menu

In the Properties window, select the Extended Properties page, enter the Name and Value for that object. To save entered information, click the OK button:

Extended Properties window

Extended properties can be created for the following types of database objects: database, table, table column, stored procedures, views, user-defined functions, triggers, constraints, rules, and table index.

To edit the SQL Server extended properties in the Properties window, click in the Value field and enter a new value for the objects. Another method to change the value is to click on the ellipse () button on the right side of the Value field and, in the Extended property value box, enter a new value:

Extended property value box

To delete a SQL Server extended property, under the Properties window, simply click the Delete button:

Delete SQL Server extended properties

Modifying SQL Server extended properties with SQL Server stored procedures

Adding SQL Server extended properties

To add extended properties, use the sp_addextendedproperty stored procedure. Let’s create a database with one table and two columns in it and show how to add descriptions for a database, table, and columns:

CREATE DATABASE SQLDatabase;
GO

IF OBJECT_ID('MyTest', 'U') IS NOT NULL
  DROP TABLE SQLTable;
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE SQLTable (
  id INT
  ,Name VARCHAR(20)
  );
GO

Now, let’s add SQL Server extended property for the database. In a query editor, execute the code below:

EXEC sp_addextendedproperty @name = N'Database Description'
  ,@value = N'Holds information about employees';
  • @name – represent the name of the Extended Property and cannot be the NULL value
  • @value – represent the value or description of the property and cannot be lager then 7500 bytes

To set extended property for a table, execute the following code:

EXECUTE sp_addextendedproperty @name = N'PurposeOfTable'
  ,@value = N'Holds data about each employee'
  ,@level0type = N'SCHEMA'
  ,@level0name = 'dbo'
  ,@level1type = N'Table'
  ,@level1name = 'SQLTable';
  • @level0type – represents in our case Schema
  • @level0name – represents the value (name) of a Schema, in our case that is dbo
  • @level1type – represents in our case Table object
  • @level1name – represents the name of a table, in our case that is SQLTable

The following code will add the SQL Server extended properties to id and Name columns under the SQLTable table:

Extended property for id column:

EXECUTE sp_addextendedproperty @name = N'PurposeOfIDColumn'
  ,@value = N'Holds ID of an employee'
  ,@level0type = N'SCHEMA'
  ,@level0name = 'dbo'
  ,@level1type = N'Table'
  ,@level1name = 'SQLTable'
  ,@level2type = 'Column'
  ,@level2name = 'id';

Extended property for Name column:

EXECUTE sp_addextendedproperty @name = N'PurposeOfNameColumn'
  ,@value = N'Holds name of employee'
  ,@level0type = N'SCHEMA'
  ,@level0name = 'dbo'
  ,@level1type = N'Table'
  ,@level1name = 'SQLTable'
  ,@level2type = 'Column'
  ,@level2name = 'Name';
  • @level2type – represents in this case the Column object
  • @level2name – represents the name of the column, in our case that are id and Name columns

Updating SQL Server extended properties

To make changes of an extended property, use the sp_updateextendedproperty stored procedure. Now, let’s edit and chance description for the column Name. In the query editor, execute the following code:

EXECUTE sp_updateextendedproperty @name = N'PurposeOfNameColumn'
  ,@value = N'Holds first name of employee'
  ,@level0type = N'SCHEMA'
  ,@level0name = 'dbo'
  ,@level1type = N'Table'
  ,@level1name = 'SQLTable'
  ,@level2type = 'Column'
  ,@level2name = 'Name';

Deleting SQL Server extended properties

For deleting an extended property, the sp_dropextendedproperty can be used. To delete an extended property for specific column, execute the following code:

EXECUTE sp_dropextendedproperty @name = N'PurposeOfNameColumn'
  ,
  --@value = N'Holds first name of employee',
  @level0type = N'SCHEMA'
  ,@level0name = 'dbo'
  ,@level1type = N'Table'
  ,@level1name = 'SQLTable'
  ,@level2type = 'Column'
  ,@level2name = 'Name';

Executing this removes the property from the database.

Modifying SQL Server extended properties with ApexSQL Search

Using the ApexSQL Search extended property editor, adding, updating, and deleting descriptions to database objects can be done very quickly and easily.

To initiate this feature, in Object Explorer (SSMS) or in Server Explorer (Visual Studio), select a database and under the ApexSQL Search main menu, choose the Edit extended properties command:

The main menu of ApexSQL Search add-in Visual Studio with the Edit extended properties option highlighted

Another way to start the Extended property editor window is via the Edit extended properties command that is in the Object/Server Explorer context menu:

Edit extended properties command

Or just select a database in the Object/Server Explorer pane and press the Ctrl+Shift+Alt+U shortcut, and the Extended property editor window will appear:

Extended property editor window

Navigate to the objects for which you want to add/modify the description, and click the ellipse (…) button in the Description column:

Modify SQL Server extended properties

In the Edit extended property window, type the description and click the OK button to finish off editing the object:

Edit extended property window

Now, in order to save the changes, click the OK button on the Extended property editor window:

Commit the changes by click the OK button under the Extended property editor window

To delete SQL Server extended properties, in the Description column, find the values that should be deleted, click in the field and press the Delete or Backspace key from the keyboard. Click the OK button to commit changes:

Delete SQL Server extended properties

Additionally, the Filter row feature can be used to filter out unneeded objects:

Filter row feature

To modify SQL Server extended properties in other databases, in the Extended property editor window, click the Connection button, in the Connect to SQL Server window, set connection parameters to other database and click the Connection button:

Connect to SQL Server window

 

February 21, 2020