Adding Extended Properties to SQL*Server Databases Using PowerShell

When you are working as part of a team that manages a few hundred SQL*Server instances; sometimes, it becomes hard to keep track of all the people who need to be notified when you plan for database migrations and such. Also, if there is an issue that needs their input ($’s); we do not want to send it to the wrong person now, would we. Maintaining the information in excel sheets is good but, wouldn’t it be nice to have the information available on the DB itself? We can get all the information in one shot and send out e-mails to the concerned (correct) individuals.

To work around this problem, we added extended properties to all the user databases that we have on the instances. After I did this on about 5 databases in an instance; I was bored and wanted to see if there was any other way of doing this other T-SQL. Sure, there is and it’s in PowerShell, I couldn’t ask for more. It was good to walk off the beaten path and see explore new things but, it did not take me too long to figure out why documentation is boring; even PowerShell could not help me out much here.

The script is pretty straightforward though:

“Connect to the SQL*Server instance, Loop through all users databases and if a database does not have extended properties; Create a new extended properties object, Attach the object to the current DB and type in the values required.”

# Load required assemblies
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") | Out-Null;
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMOExtended")| Out-Null;

try
{
	$smoConn = New-Object ('Microsoft.SqlServer.Management.Smo.Server') $sqlServer
	$smoConn.ConnectionContext.ConnectTimeout = $commandTimeout
	$smoConn.ConnectionContext.Connect()

	$sqlServerDB = $smoConn.Databases

	foreach($db in $sqlServerDB){
		#Write-Host "Processing DB: " $db -Fore Magenta
		[String]$dbName = $db.Name
		if(  "master", "model", "msdb", "tempdb", "pubs", "NorthWind", "AdventureWorks" -notcontains  $dbName  )
		{
			Write-Host "Working on DB ->" $db  -Fore Green
			$xPropCount = $db.ExtendedProperties.Count
			if($xPropCount  -eq 0){
				$ownrExtProp = New-Object Microsoft.SqlServer.Management.Smo.ExtendedProperty
				$ownrExtProp.Parent = $db
				$ownrExtProp.Name = 'Owned By'
				$ownrExtProp.Value =  Read-Host "Enter the Name of the team that owns the DB: "
				$ownrExtProp.Create()

				$cntctExtProp = New-Object Microsoft.SqlServer.Management.Smo.ExtendedProperty
				$cntctExtProp.Parent = $db
				$cntctExtProp.Name = "Contact"
				$cntctExtProp.Value =  Read-Host "Enter the E-mail address of the group that owns the DB: "
				$cntctExtProp.Create()
			}

			foreach ($xProperty in $db.ExtendedProperties){
					Write-Host  " " $xProperty "->" $xProperty.Value -Fore Yellow
			}
		}
	}
}
catch
{
	$_ | fl * -Force
}

<# 	name	value 	OwnedBy	SqlChow 	Contact [email protected] #>

Since, we put in so much work documenting this stuff I would have liked to add a nightly job that would read these properties and keeps a central repository updated with the latest changes to the DB. Maybe…that is an overkill.

About SqlChow

By profession, I’m a SQL Server Database Administrator. I love to poke my nose into different corners and see how stuff looks in there. I keep looking for new things to do as my mind refuses to settle on one topic.