#PSTip Reading extended properties on database objects using SMO

Note: This tip requires PowerShell 2.0 or above.

Extended properties are an useful but, under utilized feature in SQL server. In an earlier tip, we saw that you can add extended properties to databases and other objects. You can also add them to columns in your tables, and many other objects in your databases.

Now for the fun part. Let us say we have a database ‘sqlchow’ in your instance. We can retrieve the extended properties, if they have been set, using the following code:

Add-Type -AssemblyName "Microsoft.SqlServer.Smo, Version=, Culture=neutral, PublicKeyToken=89845dcd8080cc91"
$server = New-Object Microsoft.SqlServer.Management.Smo.Server $env:COMPUTERNAME
$server.Databases["sqlchow"].ExtendedProperties | Select Name, Value, State

	Name                   Value                                   State
	----                   -----                                   -----
	CreatedBy              sqlchow                              Existing
	CreatedOn              2013-08-17 13:00:06                  Existing
	Generate Documentation 1                                    Existing
	Purpose                Demo creation of extended properties Existing

In the next tip let us see how we can alter extended properties on an object.

Filed in: Columns, Tips and Tricks Tags: , , ,
© 2018 PowerShell Magazine. All rights reserved. XHTML / CSS Valid.
Proudly designed by Theme Junkie.
%d bloggers like this: