Working with SQL Server Extended Properties

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, etc. In this tip, we will show how to 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:

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

You can create Extended Properties for the below database objects. However, in this tip, we will focus on column level Extended Properties.

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

Use Extended Properties 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 expand the Columns. Right-click on column “sno”, select Properties, and go to the Extended Properties page.

The below screenshot shows the Extended Properties for column “sno” are empty after creating the table. To add an Extended Property, 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. Execute the stored procedure below with these parameters:

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

Necessary parameters to execute sp_addextendedproperty include:

@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 set to ‘dbo’
@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.

Image2 - Description: Added 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 can fetch the data as per the request. Many documentation automation projects query these tables and use the data 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, which is the following query, 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. Use the following query to get the Extended Property for the column. Note: This was tested on SQL 2012, 2014, 2016, and 2017, including all the 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:

Column Level Extended Properties – All

If we want column level Extended Properties for all columns in the database, run the following code:

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.

Database Level Extended Properties – All

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

Table Level Extended Properties – All

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

Stored Procedure Level Extended Properties – All

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'

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. Use NULL as a parameter for the object name to return multiple extended properties. However, it is not as straightforward as you think it should be to use this function.

In the master database, the function is 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 can 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 retrieves the row based on the Name property which is ‘SNO’ in our example.

The below screenshot 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

Query the data again using the function to show the removal of the entry.

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

Conclusion

We now know how to add, update, and delete Extended Properties in SQL Server. Also, we know how to use fn_listextendedproperty to query the available Extended Properties.

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

Next Steps

Details about the SQL Server System Catalog Views are available: System catalog views (Transact-SQL).

Resources

Community

Subscribe