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:
Extremely simple Extended Properties
|
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:
Table Extended Properties
|
Object type |
Object Name |
Classification |
Comment |
|
Table |
myTable |
Description |
This is my table! |
|
Table |
myTable |
Developer |
Adam |
And finally, to describe a column:
Column Extended Properties
|
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.
Extended Properties Hierarchy
|
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.
