Extended Properties

Phil Factor has a detailed article on 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 was an interesting idea but there was so little tooling available to make them really useful.  I don’t see that changing.

Related Posts

Creating Dynamic Pivot Tables

Ben Richardson shows how to use dynamic SQL to create pivot tables with arbitrary numbers of pivot elements: The headings of the columns are the individual values inside the city column. We specified these values inside the pivot operator in our query. The most tedious part of creating pivot tables is specifying the values for […]

Read More

Matrix Transposition In T-SQL

Phil Factor has some fun transposing a matrix using T-SQL: What I’m doing is simply converting the table into its JSON form, and then using this to create a table using the multi-row VALUES  syntax which paradoxically allows expressions. The expression I’m using is JSON_Value, which allows me do effectively dictate the source within the table, via […]

Read More


October 2016
« Sep Nov »