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:
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 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:
To delete a SQL Server extended property, under the Properties window, simply click the Delete button:
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:
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:
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:
Navigate to the objects for which you want to add/modify the description, and click the ellipse (…) button in the Description column:
In the Edit extended property window, type the description and click the OK button to finish off editing the object:
Now, in order to save the changes, click the OK button on 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:
Additionally, the Filter row feature can be used to filter out unneeded objects:
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:
February 21, 2020