Reading, Writing, and Creating SQL Server Extended Properties

There is a great gulf between wanting to document your database properly with extended properties and actually doing it. Extended Properties have many uses but they aren't easy to use. Phil Factor is on a mission to make it easier for ordinary mortals to use extended properties as intended, to aid the database development process.

What I’d like to do in this article is to explain something about SQL Server extended properties, why they are so important, and illustrate some ways of working with them, scripting them, and maintaining them.

Why Extended Properties?

Before SQL Server 2000, there was a very awkward problem with SQL Server: It was impossible to attach additional properties to a database object such as a database, schema, table, column, index or so on. Imagine that you wanted to attach a note to a database to provide the current version, and maybe list the previous deployments to bring this database up to the version. You couldn’t do it. These properties are important for documentation; particularly for tables and their associated properties such as columns and indexes because, in these cases, SQL Server doesn’t, and can’t, preserve the CREATE source with all its liberal comments and documentation.

There were then some ways around the problem, of course. Obviously, you could attach information about a database by creating a stored procedure or view to give you what amounted to global constants such as the version number, or the revisions that had taken place. This wouldn’t tackle other problems such as keeping the documentation about the table together with the table; the same applied to indexes, columns and other database objects where the objects source was not retained within the database. Then, as now, we also needed occasionally to attach additional information to columns to apply masks, application-validation information such as regexes, formatting information, caption information or other such things. Above all, it was documentation that was we needed. Then came extended properties.

Extended properties are easy to read, thanks to a useful system view. Sys.extended_properties. However, they are a pain to create, update and delete; they rely on special stored procedures that have a syntax that isn’t at all intuitive for those of us without mutant mental powers. They have a limit of 7,500 characters but are actually stored in a SQL_variant so that DateTime, Approximate numeric, exact numeric, character, Unicode and binary information can be stored in it. Most of us use some sort of tool such as SSMS to maintain this documentation rather than to do it via SQL. The SQL is cumbersome.

Extended Properties within SQL Server are complicated because of the way that SQL Server understands all the components/entities/things of a database. There is no single numeric identifier that will guarantee to uniquely identify a component of a database. Certainly, there is the object_ID of the system catalog views, but then only certain components are considered to be objects. Columns, for example, aren’t objects, and nor are indexes. Parameters aren’t either. Yet these are the very things that need documentation the most. SQL Server relies on a four-part dot-based identification system to reference any component. The name of each type is separated by dots. It will work fine, but because of the ‘uniqueness’ rules of SQL Server, you need to know what each component represents. This means that you also need a list of the types. The problem with this is that the names of database objects types aren’t consistent across the product and SSMS. In the catalog views, ‘Functions’ consist of several object types (TF, FN, IF, FS or FT) as can a ‘Procedure’ (P, PC, RF or X). The information schema catalog uses another different jargon. The diffeences in the terms used by SQL Server and SMO makes things more difficult. .

The four functions or procedures that allow you to maintain the extended properties, sys.fn_listextendedproperty, sp_addextendedproperty, sp_dropextendedproperty and sp_updateextendedproperty, aren’t easy to use. Although you can generally avoid the listing function, you can’t avoid the others easily. You don’t just provide the path to the entity/thing/component, but you have to parse both references into their component levels to provide the parameters to these procedures.

SMO grappled with this very problem and came up with a unique identifier, called the Uniform Resource Name (URN). This does the trick, though it complicates matters by using a subset of an XPath expression. It is a subset of an SMO query string that you can, I’m told, use in SMO to create collections of anything you specify that is an SMO object within the database. The difference is that the URN is a unique reference. With an easily serialised reference such as a URN, it is easy to get hold of a database ‘thing’ and document it by means of an extended property. Once you have any live SMO database object, you can assign a value to an existing extended property or create a new one using its ExtendedProperty value.

SMO has its own terms for the various classes of entities/things that it has in collections. You can easily get a list of them with this PowerShell code.

You will see that they are differences from the terms used by SQL Server

Editing and creating extended properties in PowerShell.

If you have a serialized version of a unique URN reference to an object, then you have all that you need to instantiate a server object. (Microsoft.SqlServer.Management.SMO.Server) and made a connection with it. You then get any object you wish using the unique reference (URN).You can then read, or write to, any extended property for any component within any database on the server just by specifying Uniform Resource Name (URN) to the extended property of that object. So we can find the value of an MS_Description property on a view by …

…and you can change an existing value simply by assigning to it. You can create a new property perfectly easily. Here is a PowerShell function that does this for any SMO object.

This is convenient. It is much easier than using a SQL Server object_ID, even where we are dealing with a database object. Just for the record, if you happen to have an instantiated database object, and you want to access the extended property of a view with an object_ID of 39671189 you could do this.

You can put extended properties on almost any database ‘thing’. (Long sentence alert!) What do I mean by a ‘thing’? Actually, you can put extended properties on the Database itself, any Object ( which would include Aggregate functions, check constraints, defaults (constraint or stand-alone),foreign key constraints, SQL scalar functions, Assembly (CLR) scalar-functions, ,Assembly (CLR) table-valued functions, SQL inline table-valued functions, SQL Stored Procedures, Assembly (CLR) stored-procedures, Plan guides, primary key constraints, Rules (old-style, stand-alone), Replication-filter-procedures, System base tables, Synonyms or Sequence objects), columns, Parameters, Schemas, Database principals, Assemblies, Types, Indexes, XML schema collections, Message types, Service contracts, Services, Remote service bindings, Routes, Dataspaces (filegroups or partition schemes), Partition functions, Database files or Plan guides. It is quite a list. This is why the URN is so useful for defining the database component that you’re interested in.

Listing out extended properties in SQL

The process of listing out the extended properties in a database is a bit tedious but perfectly reasonable. Basically, you use the sys.extended_properties table and join to other system tables as appropriate to identify precisely what it is you’re attaching a property to. I find this query handy. I haven’t added some of the more obscure database components/things because I’ve never felt the urge to document them and have never found a soul who has. As well as the extended property, I’ve added a reference to the ‘thing’ or object to which the property is attached as well as a dot reference by name. You’d need these if you wanted to use SQL Server’s stored procedures.

In use, I generally just turn this into a view and select just the ones that I need. This is another reason why I add the ‘thing’ column as well as the ‘path’ column. It makes WHERE clauses easier.

This is what a result looks like…

ProcResults

Scripting out extended properties in PowerShell

PowerShell allows you to script any object, both the create or drop script. Luckily, SMO considers an extended property to be an object so it is pretty easy. For SQL Server, an extended property is just a property, but SMO considers it an object that can be scripted and which even has a URN. There are plenty of ways of gathering up all the ‘things’ that can have an extended property but we’ll get everything that is schema-based. Sure, there are some ‘things’ that can assigned a property but this would just bulk up the routine. Here, I’ll give you the plain script so you can play around with it interactively, but in real life you’d wrap this up in a function. And pass to it, as a parameter, a connected server object and a database name.

This would give you something like this- in the case of AdventureWorks…(just an extract!)

Which would look like this in SSMS after a touch of formatting

The first thing I’d want to experiment with is to change that last part of the script

To this, which will produce the SQL scripts to drop all the extended properties.

…like this. (just a sample, of course)

Getting Extended property information.

So far we have managed to get the create and drop SQL Scripts from PowerShell. Because we are, in effect, iterating through all the extended properties to do this, we can actually perform the drop on them directly, but I’d be very careful with this! Just to finish this section, I’d like to show how we’d get a listing of all the ‘thing’s in a database that can have extended properties, including the URN so we can alter any extended property that we want, and see what hasn’t been documented yet.

I’m using YAML to serialise all this information in order to archive it. By using this, I can see what isn’t and what is documented. Here is a short sample. As with quite a lot of SMO, this type of script is very slow with a database of any considerable size.

Conclusions

Extended properties can be habit-forming once you’ve got confidence with them. When all the aspects and entities of a database are documented, it makes the maintenance effort a lot easier. That index for example. You can save that half-minute spent puzzling out the how and why. You can use automated processes to attach dependency information to tables, functions and procedures. You can preserve table or index header comments in EPs, or even store the commented table-sources at build or deployment time. Extended properties are a great solution, and once you’re familiar with them, you’ll suddenly find the problems they solve.

  • 8031 views

  • Rate
    [Total: 13    Average: 4.6/5]