Working with SQL Server Extended Properties

By: Jayendra Viswanathan   |   Updated: 2018-04-13   |   Comments (3)   |   Related: > Database Design

Problem

SQL Server can store a large volume of data in relational formats which is great for the business, but business users and developers also have needs to store documentation and information related to the SQL Server objects. One way to do this is to use Extended Properties which allows you to save information about the objects such as what it's for, specific formats like phone format, date format, description of objects, URLs, website links and so on. In this tip we will show how we can add Extended Properties.

Solution

Extended Properties is a unique feature in SQL Server to store more information about database objects.  In this article we will see how to:

1. Add, Update and Drop Extended Properties.
2. Extract the Extended Properties from sys.objects and sys.extended_properties tables.
3. How to use function FN_LISTEXTENDEDPROPERTY() to extract Extended Properties.

Extended Properties can be created for the below database objects, but in this tip, we are going to focus on column level Extended Properties.

• Database
• Stored Procedures
• User-defined Functions
• Table
• Table Column
• Table Index
• Views
• Rules
• Triggers
• Constraints

Extended Properties can be used to:

• Specify a caption for a table, view, or column.
• Specify a display mask for a column.
• Display a format of a column, define edit mask for a date column, define number of decimals, etc.
• Specify formatting rules for displaying the data in a column.
• Describe a specific database objects for all users.

Example of Creating Extended Properties

Let's create a table that has two columns “sno” and “myName”.

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

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE MyTest (sno int, myName char (20))
GO	

To look at the Extended Properties for a column, in SSMS expand Tables, find the table we created and then expand the Columns. Then right click on column "sno" and select Properties and go to the Extended Properties page.

The below screenshot shows the Extended Properties for column "sno" is empty after the table is created. If you want to add an Extended Property you can just type in the Name and Value on the screen below.  I will also show how to do this with T-SQL.

Add SQL Server Extended Property with sp_addextendedproperty

We can also use sp_addextendedproperty to add an Extended Property. The below stored procedure needs to be executed with parameters as follows.

exec sp_addextendedproperty  
     @name = N'SNO' 
    ,@value = N'Testing entry for Extended Property' 
    ,@level0type = N'Schema', @level0name = 'dbo' 
    ,@level1type = N'Table',  @level1name = 'mytest' 
    ,@level2type = N'Column', @level2name = 'sno'
go			

A few parameters are required to execute sp_addextendedproperty.

• @name is ‘SNO’ in our case. This cannot be null. This is the name of the Extended Property.
• @value is the value or description of the property and it cannot exceed 7500 bytes.
• @level0type in our case ‘Schema’ and @level0name is the value is set as 'dbo' as the value
• @level1type in our case ‘Table’ and @level1name is ‘mytest’
• @level2type in our case ‘Column’ and @level2name is ‘sno’

The below screen shows the added Extended Property using sp_addextendedproperty. It shows the name and value for the Extended Property.

Querying SQL Server Extended Properties

The sp_addextendedproperty will create rows in the sys.extended_properties table, by storing data in this table SQL Server has the ability to fetch the data as per the request. In many documentation automation projects, these tables can be queried and the data can be used for documentation purposes.

Below is the query to extract data about the ‘MyTest’ table.  We can see the object_id value for the table.

select * 
from sys.tables  
where name = 'MyTest'			

Below we can query sys.extended_properties to get more information. We can also see the major_id matches the object_id above.

select * 
from sys.extended_properties 
where NAME = 'SNO'		

If we run a SQL Server Trace we can capture what SQL Server uses in SSMS, we find the following query is used by SSMS to pull the data for column level Extended Properties. The below script is from a SQL Server 2017 instance.

exec sp_executesql N'SELECT
p.name AS [Name],
CAST(p.value AS sql_variant) AS [Value]
FROM
sys.tables AS tbl
INNER JOIN sys.all_columns AS clmns ON clmns.object_id=tbl.object_id
INNER JOIN sys.extended_properties AS p ON p.major_id=tbl.object_id AND p.minor_id=clmns.column_id AND p.class=1
WHERE
(p.name=@_msparam_0)and((clmns.name=@_msparam_1)and((tbl.name=@_msparam_2 and SCHEMA_NAME(tbl.schema_id)=@_msparam_3)))
OPTION (FORCE ORDER)
',N'@_msparam_0 nvarchar(4000),@_msparam_1 nvarchar(4000),@_msparam_2 nvarchar(4000),@_msparam_3 nvarchar(4000)',@_msparam_0=N'SNO',
@_msparam_1=N'sno',@_msparam_2=N'MyTest',@_msparam_3=N'dbo'

We can simplify this and use the following query to get the Extended Property for the column.  This was tested on SQL 2012, 2014, 2016 and 2017 as well as all of the other queries below.

SELECT
   SCHEMA_NAME(tbl.schema_id) AS SchemaName,	
   tbl.name AS TableName, 
   clmns.name AS ColumnName,
   p.name AS ExtendedPropertyName,
   CAST(p.value AS sql_variant) AS ExtendedPropertyValue
FROM
   sys.tables AS tbl
   INNER JOIN sys.all_columns AS clmns ON clmns.object_id=tbl.object_id
   INNER JOIN sys.extended_properties AS p ON p.major_id=tbl.object_id AND p.minor_id=clmns.column_id AND p.class=1
WHERE
   SCHEMA_NAME(tbl.schema_id)='dbo'
   and tbl.name='MyTest' 
   and clmns.name='sno'
   and p.name='SNO'
      

Here is the output.

Get all column level Extended Properties

If we want to get column level Extended Properties for all columns in the database, we could run the following.

SELECT
   SCHEMA_NAME(tbl.schema_id) AS SchemaName,	
   tbl.name AS TableName, 
   clmns.name AS ColumnName,
   p.name AS ExtendedPropertyName,
   CAST(p.value AS sql_variant) AS ExtendedPropertyValue
FROM
   sys.tables AS tbl
   INNER JOIN sys.all_columns AS clmns ON clmns.object_id=tbl.object_id
   INNER JOIN sys.extended_properties AS p ON p.major_id=tbl.object_id AND p.minor_id=clmns.column_id AND p.class=1
      

Here are some other example queries.

Get all database level Extended Properties

SELECT
   DB_NAME() AS DatabaseName,
   p.name AS ExtendedPropertyName,
   p.value AS ExtendedPropertyValue
FROM
   sys.extended_properties AS p
WHERE
   p.major_id=0 
   AND p.minor_id=0 
   AND p.class=0
ORDER BY
   [Name] ASC

Get all table level Extended Properties

SELECT
   SCHEMA_NAME(tbl.schema_id) AS SchemaName,	
   tbl.name AS TableName, 
   p.name AS ExtendedPropertyName,
   CAST(p.value AS sql_variant) AS ExtendedPropertyValue
FROM
   sys.tables AS tbl
   INNER JOIN sys.extended_properties AS p ON p.major_id=tbl.object_id AND p.minor_id=0 AND p.class=1

Get all stored procedure level Extended Properties

SELECT
   SCHEMA_NAME(sp.schema_id) AS SchemaName,	
   sp.name AS SPName, 
   p.name AS ExtendedPropertyName,
   CAST(p.value AS sql_variant) AS ExtendedPropertyValue
FROM
   sys.all_objects AS sp
   INNER JOIN sys.extended_properties AS p ON p.major_id=sp.object_id AND p.minor_id=0 AND p.class=1
WHERE
   sp.type = 'P' OR sp.type = 'RF' OR sp.type= 'PC'

Get Extended Property Using fn_listextendedproperty

We can also use the fn_listextendedproperty function to get a list of Extended Properties. The function returns objtype, objname, name with datatype sysname and value as sql_varient. NULL can be used as a parameter for the object name to return multiple extended properties, but it is not as straight forward as you would think it should be, to use this function.

The function can be found in the master database under Programmability > Functions > System Functions.

Execute the below query to get the Extended Property.

SELECT *
FROM ::fn_listextendedproperty ('SNO', 'Schema', 'dbo', 'Table', 'mytest', 'Column', 'sno')	

In the above query we notice that the first parameter is the property_name “SNO” and the other 6 parameters are object level and object type from level 0 to 2 in our example.  Below is the output:

Update an Extended Property with sp_updateextendedproperty

We can use sp_updateextendedproperty stored procedure to update the value of existing extended property.

exec sp_updateextendedproperty
     @name = N'SNO' 
    ,@value = 'SNO ID must be unique.' 
    ,@level0type = N'Schema', @level0name = 'dbo' 
    ,@level1type = N'Table',  @level1name = 'mytest'
    ,@level2type = N'Column', @level2name = 'sno'
GO			

The update stored procedure is used to update the value in the extended property as “SNO ID must be unique”. The update stored procedure is similar to the add stored procedure which accepts the same parameters and its retrieves the row based on the Name property which is ‘SNO’ in our example.

The below screen print shows the updated text in our example.

We can query the data again using the function to show the change.

SELECT *
FROM ::fn_listextendedproperty ('SNO','Schema', 'dbo', 'Table', 'mytest','Column', 'sno')	

Drop Extended Property with sp_dropextendedproperty

The sp_dropextendedproperty removes an extended property from the database. The following shows how to remove an entry.

exec sp_dropextendedproperty @name=N'SNO'
  ,@level0type = N'Schema', @level0name = 'dbo' 
  ,@level1type = N'Table',  @level1name = 'mytest' 
  ,@level2type = N'Column', @level2name = 'sno'
go			

We can query the data again using the function to show the entry has been removed.

SELECT * 
FROM ::fn_listextendedproperty ('SNO','Schema', 'dbo', 'Table', 'mytest','Column', 'sno')
			

Conclusion

We have seen the how to add, update and delete Extended Properties in SQL Server. Also, we saw how fn_listextendedproperty can be used to query the available Extended Properties.

Extended properties are a useful feature in SQL Server which can be used for documentation and content purposes. The properties can be updated for tables, views, triggers and so on. Developers can use this feature for extensive database objects which can be used for reference for many SQL Server objects.

Next Steps

• Details about the SQL Server System Catalog Views are available here.

About the author

Jayendra is a Project Leader with many years of IT experience. He has strong knowledge in software development and project management.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips