Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

Next Webcast - Manage and Monitor SQL Server - Lots of demos!
 

Working with SQL Server Extended Properties


By:   |   Read Comments (1)   |   Related Tips: More > 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.

Image1 - Description: Blank Properties

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.

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 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'		
query extended properties

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
([email protected]_msparam_0)and(([email protected]_msparam_1)and(([email protected]_msparam_2 and SCHEMA_NAME(tbl.schema_id)[email protected]_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.

query extended properties

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.

Image3 - Description: function path

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:

query extended properties

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.

Image4 - Description: Updated SNO

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

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

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')
			
query extended properties

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.


Last Update:


next webcast button


next tip button



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

View all my tips





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Friday, April 13, 2018 - 6:53:58 AM - Peter Tilsted Back To Top

 

Extended properties is a real  gem that too many are neglecting

It is a little cumbersome to make it work with no hassle but i have earlier found this article:

https://www.red-gate.com/simple-talk/sql/sql-tools/towards-the-self-documenting-sql-server-database/

the article is quite old, but the principles are still valid.

We use the stored procedure described in the articel, with some modfications of our own.

we have added a compulsory call of the stored procedure to all our templates for DDL changes, and had an initial run for existing not described objects.

Now our databases are always described in a usable and up to date manner, that we can extract with the different documentation tools

 


Learn more about SQL Server tools