PowerShell and Extended Properties

I use Extended Properties on databases and their objects all the time. They are a great way to include information about the object – I use them for versioning the database, detailing what a column is used for and so on. They can be a little tricky to set, but it’s really not bad once you learn how. Ken Simmons, a SQL Server MVP has a fantastic article here that explains more: http://cybersql.blogspot.com/2010/07/extended-property-awesomeness.html


Although it’s not as simple as I would like to set the properties using PowerShell, it’s really trivial to read them. I do this to make system documentation in an HTML page. Open a SQL Server 2008 Powershell provider (sqlps.exe) and CD to any “directory” of a database or tables, views, whatever. Then you can just type this:

DIR | select-object –property name, Extendedproperties


As always, do this on a test system.


Script Disclaimer, for people who need to be told this sort of thing:

Never trust any script, including those that you find here, until you understand exactly what it does and how it will act on your systems. Always check the script on a test system or Virtual Machine, not a production system. Yes, there are always multiple ways to do things, and this script may not work in every situation, for everything. It’s just a script, people. All scripts on this site are performed by a professional stunt driver on a closed course. Your mileage may vary. Void where prohibited. Offer good for a limited time only. Keep out of reach of small children. Do not operate heavy machinery while using this script. If you experience blurry vision, indigestion or diarrhea during the operation of this script, see a physician immediately.