I imagine that most SQL Server database developers and DBAs have used extended properties to add comments to the database objects which they create. However my experience is that this surprisingly useful aspect of the database metadata is often either underused or even avoided. This could be for a variety of reasons, some of which could include:
- The system stored procedures which manipulate extended properties are initially unintuitive to use.
- Collections of extended properties once they have been added to a database are not immediately accessible.
- Adding extended properties is perceived as laborious and not suited to documenting hundreds or thousands of database objects quickly and easily.
- Updating multiple extended properties in the database metadata can be equally hard work.
- Exporting extended properties to use in external documentation (which normally means Word or Excel) can be a long-winded process.
So, in order to dispel any negative perceptions which may exist or persist, I intend to take the reader on a tour of extended properties which, I hope, will convince you that they are:
- Easy to understand
- Simple to create, modify and delete.
- Not at all difficult to extract and use in other programs.
Given the amount of information and code to cover, this will be spread over four short articles:
- An introduction to extended properties.
- Extracting extended properties, and a macro to generate a Word document of extended properties.
- Modifying and deleting extended properties.
- Industrial-strength documentation with extended properties.
Yes, I know that there are plenty of resources about the system view, stored procedures and function which SQL Server uses to manage extended properties, and there are some excellent articles available (not least David Poole's recent article on SQLServerCentral). However, I thought that there was room for a step by step introduction to the subject with lots of practical examples - so here goes.
Let's begin at the start, what exactly are we talking about?
We are talking about ensuring the longevity of your database, by making sure that all your hard work spent creating and modifying a complex system is commented and noted inside the database itself. Nothing can write the documentation for you, but SQL Server can store comments on a specified subset of database objects inside the database metadata. There is no limit (as far as I can see) to the number of separate comments that you can add to a database object, and each one can have a separate heading, or classification. Each comment can be up to 7500 characters (whether you are using Unicode characters or not). Each classification is a sysname data type so has a 128 unicode character limit. Names can also include blank or non-alphanumeric character strings, and binary values - but in practice simple alphanumeric one or two word titles seem to be the most useful.
If you are feeling adventurous, you can use extended properties in many other imaginative ways. How about:
- Storing data lineage information.
- Storing versioning information (last modified by, succinct history of change reasons).
- To store tooltips for user interfaces.
- To store tooltips in multiple languages, using the name as a linguistic classification.
- To store column headings for user interfaces.
- To store column headings in multiple languages, using the name as a linguistic classification.
- And many, many more!
However, I will presume that most developers and DBAs use extended properties for database documentation, and so this will be the main focus of these articles.
A quick run through extended properties and SSMS
Before we delve into code, and into code tricks and traps, it is worth noting in the interest of completeness, that you can enter extended properties for many (but not all) objects using SSMS. This has the following advantages:
- Quick for ad-hoc creation and modification of extended properties.
- Easy to see all the extended properties for a single object.
And the following disadvantages:
- Very slow if you want to document dozens - or hundreds - of objects.
- Not all objects are accessible for extended property creations and modification this way.
- Not easy to get a more holistic view of extended properties, as you are operating only at a detail level.
The objects that you can document using SSMS are:
- Database
- Table
- Table Column
- Table Index
- View
- View Column
- View Index
- Stored Procedures
- User-defined Functions
So, to document an object using SSMS, simply:
- Right-click on the object in the object explorer
- Select Properties
- Select "Extended Properties" in the left pane
- Add a new property by entering a name (or classification) and value (or comment).
- And, of course, confirm with "OK"
Yes, I know that this is probably too easy and you know it already, but in the interest of thoroughness, I though it best to include it. Besides, it can be really useful to tweak an extended property (and the ellipse button to have a text window to edit in is very useful)!
So, with the GUI approach dealt with, it is time to dig deeper into extended properties using T-SQL. I will begin with the basic database object, the database itself. When you document the database you are including in the database metadata, the following:
| Object type | Classification | Comment |
|---|---|---|
| Database | Description | This is my database, and I am very proud of it! |
| Database | Description | Database Developer Adam |
As the metadata is limited to the database itself, you do not need to specify the database - it is a given.
It is fundamental to note that the only things that you can change are the "Classification" and the "Comment". The object is specified by the interface to the metadata (three stored procedures which we will start to look at in a few seconds), and there are only a limited set of objects which allow self-documentation using the extended properties built into SQL Server. However, when we look at all these objects, I am sure that you will agree that nearly everything that you could want to document has been made accessible by the development team.
The entire extended properties paradigm is based very simply on this model. Namely that you add a classification and a comment to any object that supports extended properties. There is no limit to the number of classifications (or property names as SQL Server calls them) that you can create, and nothing enforces their use across objects - it is up to you to apply a coherent naming convention to your extended properties. As you will see later, having a coherent set of classifications will help you create robust and resilient extended properties which can become the core of your database's documentation.
Let's take this one step further, and suppose that you want to add comments to a table. This could be explained schematically as:
| Object type | Object Name | Classification | Comment |
|---|---|---|---|
| Table | myTable | Description | This is my table! |
| Table | myTable | Developer | Adam |
And finally, to describe a column:
| Object type | Object Name | Object Sub-Name | Classification | Comment |
|---|---|---|---|---|
| Table | myTable | ID | Description | This is the ID column |
| Table | myTable | ID | Developer | Adam |
As you can see, there is a clear hierarchy to extended properties. As it is this which seems to cause the most confusion amongst the database developers I have spoken to, let's look at it in a little more detail.
Firstly, the good news, there are only four levels.
- Database level (as shown in the example above)
- Level 0 (the first sub-level, called Level0 by SQL Server)
- Level 1 (the second sub-level, called Level1 by SQL Server)
- Level 2 (the third sub-level, called Level2 by SQL Server)
These levels correspond to a drill-down hierarchy of database objects, which is extremely coherent once understood, but which can seem a little peculiar at first sight.
So when adding extended properties, it is simply a question of understanding at which level an object is situated (as far as the developers of the extended properties stored procedures are concerned), and how to reference the object.
Rather than start with the system stored procedures which let you add extended properties, I want to take a different tack and start with the hierarchy of objects. Then I will give sample code for nearly every database object which can be documented, to give you reusable code snippets to document your databases.
The extended properties object hierarchy
This is the extended properties object hierarchy, which also covers all the extended properties that I will be covering in this article. Please note that this is not an exhaustive selection, but a subset based on my experience.
| Top Level | Database |
|---|---|
| First Level | Schema Filegroup Partition Function Partition Schema DDL Trigger |
| Second Level | Table View Stored Procedure User Defined Function (Scalar inline and table-valued) Filegroup Logical file name |
| Third Level | Column (table or view) Table Constraint (Unique constraint, Default, Primary Key, Foreign Key) Index (table or view) Trigger (table or view) Parameter (Stored Procedure or User Defined Function) |
So once you know which object you want to document, all you have to do is find out the appropriate level, and add the comment using the built-in system stored procedure, adding the appropriate reference for the required levels.
The next step, therefore, is actually to do this, using the system stored procedure "sp_addextendedproperty", and seeing how it is used for the objects listed in the table above. I realise that you may not need all the code snippets given below, but preferred to give a reasonable selection so that you will, hopefully, find the one that you need to document your database objects ready to copy and paste.
Documenting the database itself
This is the simplest of all the extended properties to add, because there is no complexity about which level the object is at.
EXECUTE sys.sp_addextendedproperty @name = N'Overview' -- The "Classification" ,@value = N'Database comment' -- The "Comment" ;
As there is only one database - and the script has to be using that database for the metadata to be extended, you do not even have to specify the database name, or even the fact that you are referring to a database object. So please note that if you add extended properties without specifying any levels using "sp_addextendedproperty", then you will be adding comments at database-level.
Note:
- You can, of course, not used named parameters to add extended properties, and merely add the parameter values in the correct order. I will leave the parameters in my examples to clarify the code.
- If you are not using Unicode characters, then you can forget the "N" before the text that you are adding.
First-level objects
A basic first-level object is a schema, so here s how to add an extended property to an existing database schema:
EXECUTE sys.sp_addextendedproperty @level0type = N'SCHEMA' -- The object type ,@level0name = [Sales] -- The object name ,@name = N'Overview' -- The "Classification" ,@value = N'Main Schema' -- The "Comment" ;
The other main objects you can document with this variation on a theme of "sp_addextendedproperty"are:
Filegroup
EXEC sys.sp_addextendedproperty @level0type = N'FILEGROUP' ,@level0name = [PRIMARY] ,@name = N'Overview' ,@value = N'Filegroup comment' ;
Partition Function
EXEC sys.sp_addextendedproperty @level0type = N'PARTITION FUNCTION' ,@level0name = [Partition1] ,@name = N'Overview' ,@value = N'Partition Function Comment' ;
Partition Scheme
EXEC sys.sp_addextendedproperty @level0type = N'PARTITION SCHEME' ,@level0name = [PS2] ,@name = N'Overview' ,@value = N'Partition Scheme Comment' ;
DDL Trigger
EXECUTE sys.sp_addextendedproperty @level0type = N'TRIGGER' ,@level0name = [trg_PreventDDL] ,@name = N'Overview' ,@value = N'Comment on DDL trigger' ;
There are a couple of points worth noting here, that will also apply to the database objects to come in this article:
- The object type which is specified as "level0type" in the above examples is predefined, and though you may change the case of the object specification (unless your database objects are case-sensitive), you must use the exact object name, including any spaces.
- You do not have to enclose an object name in square brackets or single quotes - unless the object name contains spaces or other sources of object-naming confusion.
Second-level objects
Probably the most documented object in a database is the table, so let's begin with one of those:
EXEC sys.sp_addextendedproperty @level0type = N'SCHEMA' -- The object schema ,@level0name = [Sales] -- The object schema name ,@level1type = N'TABLE' -- The object type ,@level1name = [Stock] -- The object name ,@name = N'Overview' -- The "Classification" ,@value = N'This is a stock table' -- The "Comment" ;
You need to note here that all objects which are potentially schema-qualified need to have the schema specified, even if they use the default schema. So if an object is in the dbo schema - you need to specify this.
The other main objects you can document with this variation on a theme of "sp_addextendedproperty"are:
View
EXEC sys.sp_addextendedproperty @level0type = N'SCHEMA' ,@level0name = [Sales] ,@level1type = N'VIEW' ,@level1name = [vw_Invoices] ,@name = N'Overview' ,@value = N'View Comment' ;
Stored Procedure
EXEC sys.sp_addextendedproperty @level0type = N'SCHEMA' ,@level0name = [dbo] ,@level1type = N'PROCEDURE' ,@level1name = [pr_ListClients] ,@name = N'Overview' ,@value = N'Procedure comment' ;
User-defined Function
EXEC sys.sp_addextendedproperty @level0type = N'SCHEMA' ,@level0name = [dbo] ,@level1type = N'FUNCTION' ,@level1name = [ufn_GetTownCounty] ,@name = N'Overview' ,@value = N'Scalar Function' ;
This code will work for all types of user-defined Function - Scalar, Inline table and Table-Valued parameter.
Filegroup and logical file name
EXEC sys.sp_addextendedproperty @level0type = N'FILEGROUP' ,@level0name = Secondary ,@level1type = N'LOGICAL FILE NAME' ,@level1name = [ExtendedProperties_2] ,@name = N'Overview' ,@value = N'Filegroup and logical file name 2' ;
Third-level objects
A frequently documented object is a table column, so here is how to add an extended property for a column:
EXEC sys.sp_addextendedproperty @level0type = N'SCHEMA' -- The object schema ,@level0name = [Sales] -- The object schema name ,@level1type = N'TABLE' -- The object type ,@level1name = [Stock] -- The object name ,@level2type = N'COLUMN' -- The object attribute ,@level2name = [Make] -- The object attribute name ,@name = N'Overview' -- The "Classification" ,@value = N'Basic column definition' -- The "Comment" ;
Other third-level objects are:
Column View
EXEC sys.sp_addextendedproperty @level0type = N'SCHEMA' ,@level0name = [Sales] ,@level1type = N'VIEW' ,@level1name = [vw_Invoices] ,@level2type = N'COLUMN' ,@level2name = [ClientName] ,@name = N'Overview' ,@value = N'Basic column definition' ;
Table Index
EXEC sys.sp_addextendedproperty @level0type = N'SCHEMA' ,@level0name = [Sales] ,@level1type = N'TABLE' ,@level1name = [Stock] ,@level2type = N'INDEX' ,@level2name = [IX_Stock_Registration_Year] ,@name = N'Overview' ,@value = N'Index comment' ;
View Index
EXEC sys.sp_addextendedproperty @level0type = N'SCHEMA' ,@level0name = [Sales] ,@level1type = N'VIEW' ,@level1name = [vw_Stock] ,@level2type = N'INDEX' ,@level2name = [CX_vw_Stock] ,@name = N'Overview' ,@value = N'Comment on Index of Indexed view' ;
Table Constraints
EXEC sys.sp_addextendedproperty @level0type = N'SCHEMA' ,@level0name = [Sales] ,@level1type = N'TABLE' ,@level1name = [Stock] ,@level2type = N'CONSTRAINT' ,@level2name = [UQ_VehicleRegistration] ,@name = N'Overview' ,@value = N'Unique constraint' ; EXEC sys.sp_addextendedproperty @level0type = N'SCHEMA' ,@level0name = [Sales] ,@level1type = N'TABLE' ,@level1name = [Invoice] ,@level2type = N'CONSTRAINT' ,@level2name = [DF_Invoice_InvoiceDate] ,@name = N'Overview' ,@value = N'Default constraint' ; EXEC sys.sp_addextendedproperty @level0type = N'SCHEMA' ,@level0name = [Sales] ,@level1type = N'TABLE' ,@level1name = [Invoice_Lines] ,@level2type = N'CONSTRAINT' ,@level2name = [PK_Invoice_Lines] ,@name = N'Overview' ,@value = N'Primary Key' ; EXEC sys.sp_addextendedproperty @level0type = N'SCHEMA' ,@level0name = [Sales] ,@level1type = N'TABLE' ,@level1name = [Invoice_Lines] ,@level2type = N'CONSTRAINT' ,@level2name = [FK_Invoice_Lines_Invoice] ,@name = N'Overview' ,@value = N'Foreign Key' ;
Although the code is the same for each of the four types of constraint (Unique, Default, Primary Key and Foreign Key) I have given the code for each so you can test them and see that they all work.
Table Trigger
EXEC sys.sp_addextendedproperty @level0type = N'SCHEMA' ,@level0name = [Sales] ,@level1type = N'TABLE' ,@level1name = [Invoice_Lines] ,@level2type = N'TRIGGER' ,@level2name = [trg_Invoice_Lines] ,@name = N'Overview' ,@value = N'Comment on table trigger' ;
View Trigger
EXEC sys.sp_addextendedproperty @level0type = N'SCHEMA' ,@level0name = [Sales] ,@level1type = N'VIEW' ,@level1name = [vw_InvoiceLineSalePrice] ,@level2type = N'TRIGGER' ,@level2name = [NoViewDeletes] ,@name = N'Overview' ,@value = N'Comment on view trigger' ;
Stored Procedure Parameter
EXEC sys.sp_addextendedproperty @level0type = N'SCHEMA' ,@level0name = [dbo] ,@level1type = N'PROCEDURE' ,@level1name = [pr_ListClients] ,@level2type = N'PARAMETER' ,@level2name = '@ID' ,@name = N'Overview' ,@value = N'Procedure parameter comment' ;
User-defined Function parameter
EXEC sys.sp_addextendedproperty @level0type = N'SCHEMA' ,@level0name = [dbo] ,@level1type = N'FUNCTION' ,@level1name = [ufn_GetTownCounty] ,@level2type = N'PARAMETER' ,@level2name = '@ID' ,@name = N'Overview' ,@value = N'Function Parameter' ;
Notes:
- Indexes on views only apply to indexed views - and so you can only add extended properties if you have the Enterprise or Developer versions of SQL server.
- You need the rights to read the database metadata tables - but I am assuming that as a SQL Server developer, this is a given.
- Avoid adding extended property names beginning with "MS_" - this is used in views to describe the graphical positioning of query tables, for instance. It is probably best to consider this a kind of reserved term.
- You need the rights to write to the database metadata tables - but I am assuming that as a SQL Server developer, this is a given.
There are a few more extended properties which can be created, and they apply to the following objects:
- Aggregate
- Asymmetric Key
- Assembly
- Certificate
- Service Broker Contract
- Event Notification
- Message Type
- Plan Guide
- Service Broker Queue
- Remote Service Binding
- Service Broker Route
- Rule
- Service
- Symmetric Key
- Synonym
- Type
- XML Schema Collection
The decision to exclude them from this explanation is a purely personal one based on the need not to swamp the reader in code, and to focus on essential database documentation. However, as these objects follow strictly the same logic as those described above, you can easily adapt the code snippets in this article to add extended properties to them also. If you are ever overcome by the need to do this, your first port of call should be: http://msdn.microsoft.com/en-us/library/ms190243.aspx.
The attached script contains all the code given above, grouped slightly differently. It collects the code snippets by object (Table, View etc.) , to help you document all an object's attributes. The next article will deal with Extracting, updating and deleting Extended Properties.
Have fun - and I hope that you found this useful.
