A few months back, in response to new regulations like the GDPR and a growing concern about data breaches and other data privacy issues, Microsoft released a new data privacy feature in SQL Server Management Studio (SSMS). Since SSMS 17.5, users have been able to use new classification functionality that sniffs out any of the columns on your database with names that sound like they contain private data and to label them with a couple of extended properties.

Redgate are making moves in the data privacy space too. Last year they asked DLM Consultants to run a concierge program to test run their new stuff with real world users. I’ve been spending about 50% of my time over the last few months running that program and am quite excited about the stuff Redgate will be putting out over the next 6 to 12 months.

Part of that work is some functionality in Redgate SQL Estate Manager, currently hidden by a feature flag, that offers a (hopefully) improved classification experience which integrates with the Microsoft data classifications. For example, it allows users to see all the columns that SSMS wouldn’t find be default so you can check you didn’t miss anything.

One thing I’ve seen a lot of folks do during my concierge calls is clicking the buttons in either the SSMS or SQL Estate Manager UI to set up their classifications and then trying to see how the classifications are represented as extended properties by writing their own query against sys.objects. This tends to be a tedious and head-scratchy experience while someone shyly live-codes over a screen-share and gets a bit embarrassed whenever they make a typo or need to google some syntax.

With that in mind, I’ve copied a query from the SQL Estate Manager codebase (with permission) and saved it as a public GitHub gist. (Thanks Chris Lambrou for first writing it and then sharing it with me!)

If you want to query your columns and the associated privacy information saved in the extended properties, feel free to cut and paste. I also accept pull requests if you think it could be improved:

EDIT – 30th August 2019

Since writing this post Microsoft have added data classifications in SQL Server 2019 and Azure SQL DB as a first class object, rather than via an extended property.

Thank you to Aaron Hughs for providing this updated query in the comments below. For the record, I have not tested this code so please use it at your own risk – but it looks pretty sensible.

I’m reposting Aaron’s code as a GitHub Gist below for convenience – but all thanks should be directed to Aaron.