SQL Server has a, little known, built in feature called Extended Properties. In my experience, this option has gone underutilized because I wasn’t aware of its full capacity. There are a few articles out there that explain some practical usages for Extended Properties such as developer notes, documentation, labels, captions, versioning, and even data masking instructions. Extended Properties were designed to be simple yet powerful. They has the potential to be used for multiple applications but, most importantly, we can use this feature to help us protect our data.

A major priority in our industry today is protecting and tracking sensitive data. PII (Personal Identifiable Information) or PCI (Payment Card Industry) are two examples. We need to be able to track where this data lives, where it goes, and who is looking at it. One way to assist in monitoring this is to create documentation, but in our fast pace, technology driven world, the minute you hit save on the document, it is already outdated. To overcome this dilemma, the documentation can be stored in the schema and then it will move with the database as it migrates to different environments. As you use Extended Properties, you can label each database, table, and column that holds the sensitive data. This information can be queried into a report for auditors or stake holders. A more complex operational use can be to filter or even mask out the columns with the sensitive data from showing in a report or application. The process starts with creating your first Extended Property.

You can add an Extended Property to practically any object in the database but for our example we will only focus on the table level definitions. If you are a developer or DBA that wants to flag a table as sensitive because this table contains your customer’s personal information such as name, address, and phone number, where do you start? We will start with the easiest way to create your first Extended Property using SQL Server Management Studio (SSMS).

• Open SSMS
• Drill down to your database \ table
• Select the properties of your table
• In the properties menu you will see the page for Extended Properties, select this page

Now you can add your Extended Property.  There are two fields in this page that need to be configured, Name and Value. The Name field must be typed in first and used to identify the Extended Property, so it should be short, yet descriptive. I would recommend using a standard definition that will be recognized and accepted throughout your organization. For this example we could use the name ‘PII’.

• Type ‘PII’ in the first box in the Name column.

Once the Name column has been populated we can focus on the Value column. The Value field can be a longer description of the exact nature of the Name field. Keep the description focused and make sure not to include sensitive information about an object. Anyone with SELECT permissions on this table will be able to view the Extended Properties. For our example we want to communicate that this table contains PII such as our customers name, address, and phone numbers.

• Type this description in the value column “This table contains PII such as our customers name, address, and phone numbers.”

Your Extended Properties window should now look like this.

• Click on OK to save the definition. We have created our first Extended Property.

There are many tools out there on the market that document databases automatically, and all the products that I have worked on include Extended Properties in the table schema documentation because the Extended Properties are part of the metadata. SQL Server has created a method to retrieve this information. This is a system view, called sys.extended_properties.

Using the system view, sys.extended_properties, in a simple query will return a list of all the Extended Properties in a database. Make sure you direct your query window to the correct database and it will return our new definition.

Now it is really up to you how to utilize this information. You could include instructions on how to manage this data such as how to obfuscate the data when moving it to a test or development environment. You could generate a Reporting Services report to collect this information and deliver it to management. Applications or web pages could deliver this information to a popup window to notify the user about the sensitive nature of the data. There are countless applications. The key is to understand exactly how your organization can benefit from this SQL Server feature that you already pay for and take advantage of it.