I just recently started using extended properties on columns for different purposes. In my opinion, it can be used in many cases. Extended properties can be used on almost every object in SQL Server. In this blog I will focus on the columns of a table, and how they can be used in a query. In this example I will use the properties on the columns to dynamically UNPIVOT my data which is stored in a table.
So, let’s get started. In my demo database I have a table about super heroes and their super powers. The table has lots of columns(168 columns), because every super power is a separate column. It looks like this:
For different reasons I want to UNPIVOT this table. I want the super powers summarized in one column with a simple 1 or 0 as value. Because there are many super powers, and mainly because I am lazy 😊, I do not want to write the UNPIVOT query myself. So I came up with the idea to give every column an extended property, also dynamically, so that they can be used to determine my UNPIVOT query. At first I had to determine what the “Base” columns and “Unpivot” columns are. In this case, “hero_names” is my base column. All the other columns are unpivot columns. When the properties are set, it looks like this (Right click the column –> properties):
To get the extended properties that you have set, you can check out sys.extended_properties. To create and execute my UNPIVOT query I have created a stored procedure which calls out some functions to determine the SQL query. The code for this stored procedure, and the execute statement is as followed:
As you can see, In the call to execute the stored procedure, I have to give the table name, schema name and the pivot columns(results) names. In the procedure I then start collecting the base columns. Next I define the structure of all the columns with the extended property “Unpivot”. At last I build the SQLString that will be executed. For the base columns I simply look in the sys table where the action is “Base”. I then concatenate all of these columns together “,” separated. The function that defines the unpivot columns and creates the structure is a bit more complex and is defined as followed:
The function that builds the UNPIVOT query, and in this examples creates a table for the results, is defined as followed:
Now, the coding is all done, I can execute the procedure and it creates a table for me, The table and its content looks like this:
That’s it! I hope you liked this blog and that it got you inspired to use extended properties more. I for one use them a lot in my generation framework for instance. If your interested in the code, or if you have any questions, feel free to contact me.